Can I use a worksheet name in a formula?

C

Chris

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
 
P

Per Jessen

Hi Chris

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

=INDIRECT(B2&"!H2")

Regards,
Per
 
E

etaf

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.
 
C

Chris

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.
 
D

David Biddulph

If you've got spaces in the worksheet name, it needs single quotes around
it, as shown in anther branch of this thread.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top