Referencing a sheet by taking its name from a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulkaye - 20 Nov 2007 15:14 GMT

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

Reply to this Message

Gaurav Raveshia - 20 Nov 2007 15:51 GMT

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

> I'm not even sure the title of this post is correct but it's as clear
> as I could make it!
[quoted text clipped - 11 lines]
>
> Paul

Reply to this Message

Jim Thomlinson - 20 Nov 2007 15:53 GMT

You want the Indirect Formula

=Indirect(A2 & "!C7")

Signature

HTH...

Jim Thomlinson

> I'm not even sure the title of this post is correct but it's as clear
> as I could make it!
[quoted text clipped - 11 lines]
>
> Paul

Reply to this Message

Dave Peterson - 20 Nov 2007 16:47 GMT

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.

> I'm not even sure the title of this post is correct but it's as clear
> as I could make it!
[quoted text clipped - 11 lines]
>
> Paul

Signature

Dave Peterson

Reply to this Message

paulkaye - 03 Dec 2007 10:38 GMT

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

> And sometimes you need to surround the worksheet name with apostrophes:
>
[quoted text clipped - 21 lines]
>
> Dave Peterson

Reply to this Message

Rate this thread: