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

Hi Chris

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

=INDIRECT(B2&"!H2")

Regards,
Per

"Chris" skrev i meddelelsen
...
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

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" wrote in message
...
Hi Chris

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

=INDIRECT(B2&"!H2")

Regards,
Per

"Chris" skrev i meddelelsen
...
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

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" wrote in message
...
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" wrote in message
...
Hi Chris

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

=INDIRECT(B2&"!H2")

Regards,
Per

"Chris" skrev i meddelelsen
...
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

Try:

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


--
Gary''s Student - gsnu200909


"Chris" wrote:

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

.