Referencing a sheet by taking its name from a cell

[paulkaye]

I'm not even sure the title of this post is correct but it's as clear
as I could make it!

I'm creating a summary sheet of data from many other worksheets. The
worksheet names are mmm-yy (e.g. Nov-07). The top row of the summary
sheet will contain those same names, with the summary data in the
column below. I'm trying to find a way to reference the correct sheet
by utilising the name in the top row. Something like ='A2'!C7 , I
suppose, but this particular attempt didn't work.

Can someone help?

Thanks!

Paul

 

[Guest]

Assuming that you have "Nov-07"(sheet name) in Cell B2 in Summary sheet, try
the following:
=INDIRECT(B2&"!A1")
This will fetch the value from cell A1 on "Nov-07" sheet. Hope this helps.

Cheers,
GR

 

[Guest]

You want the Indirect Formula

=Indirect(A2 & "!C7")

 

[Dave Peterson]

And sometimes you need to surround the worksheet name with apostrophes:

=indirect("'" & a2 & "'!c7")

Those apostrophes won't hurt if you don't need them.

 

[paulkaye]

Hi All,

Thank you for your help - my apologies it's taken so long to try them
out! That works fine but I've found a problem, probably caused by my
not explaining fully to begin with. Whereas the a2 will change
accordingly when it is cut and pasted into other cells, the "!c7" does
not. This prevents me getting the full benefit of this technique as
the reason for it in the first place was so that I could easily make a
summary page without having to manually type the worksheet reference
into each cell.

Can any of you think of a way to solve this?

Thanks again for your time,

Paul