Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Can I use a worksheet name in a formula?

1 view
Skip to first unread message

Chris

unread,
Nov 22, 2009, 6:16:37 AM11/22/09
to
I'm using Office 2007
I have a workbook with 20+ pages. On one of them I want to use a formula
that refers to a worksheet name, but in different cells I want it to refer
to different worksheets.

I have a cell that currently has a name typed into it that matches a
worksheet name. Can I refer to the contents of that cell (INDIRECT) and use
the content to refer to the correct worksheet and then go to that worksheet
for the data?

eg. Sheet called Christmas. In cell H2 value = Pudding
on another sheet cell B2 value = Christmas. I want the formula in cell J12
to go to Sheet Christmas and give value of cell H2

Thanks
Chris

Per Jessen

unread,
Nov 22, 2009, 6:30:40 AM11/22/09
to
Hi Chris

This will get the value in cell H2 of the sheet specified in B2:

=INDIRECT(B2&"!H2")

Regards,
Per

"Chris" <chris...@zeronet.co.uk> skrev i meddelelsen
news:%23NRrGV2...@TK2MSFTNGP06.phx.gbl...

etaf

unread,
Nov 22, 2009, 6:30:57 AM11/22/09
to

Is this what you are after

=INDIRECT("'"&B2&"'!H2")

The ("'"&B2&"'!H2") builds the text you want so 'christmas'!H2 and
indirect makes it a literal reference.


--
etaf
------------------------------------------------------------------------
etaf's Profile: http://www.thecodecage.com/forumz/member.php?userid=1223
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=156270

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

Chris

unread,
Nov 22, 2009, 6:42:59 AM11/22/09
to
Thanks - I tried this before I posted and thought I was wrong because it
returned the #REF error. I checked the formatting of the cell B2 and it was
General, didn't work when I changed it to Text.

"Per Jessen" <per.j...@mail.dk> wrote in message
news:OYz05c2a...@TK2MSFTNGP04.phx.gbl...

Chris

unread,
Nov 22, 2009, 6:45:58 AM11/22/09
to
Thank you - this has worked and given me what I was hoping for

Regards

Chris

"etaf" <etaf....@thecodecage.com> wrote in message
news:etaf....@thecodecage.com...

Gary''s Student

unread,
Nov 22, 2009, 7:07:01 AM11/22/09
to
Try:

=INDIRECT(B2 & "!" & "H2")


--
Gary''s Student - gsnu200909


"Chris" wrote:

> .
>

David Biddulph

unread,
Nov 22, 2009, 7:11:48 AM11/22/09
to
If you've got spaces in the worksheet name, it needs single quotes around
it, as shown in anther branch of this thread.
--
David Biddulph

"Chris" <chris...@zeronet.co.uk> wrote in message
news:eHds3j2a...@TK2MSFTNGP05.phx.gbl...

0 new messages