Can I use a worksheet name in a formula?

[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

 

[Per Jessen]

Hi Chris

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

=INDIRECT(B2&"!H2")

Regards,
Per

 

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

 

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

 

[Chris]

Thank you - this has worked and given me what I was hoping for

Regards

Chris

 

[Gary''s Student]

Try:

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

 

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