Referencing worksheet names

[Guest]

In XP Excel I have 13 worksheet tabs, one for each month and 1 for a summary.
On Summary tab I have 12 columns with the month name in each as written on
the other 12 worksheet tab titles. Is it possible to reference a cell from
the summary page to; for example the January worksheet tab by linking the
cell on the summary sheet to the month name on the summary sheet. Ultimately
what I'm trying to do is be able to drag the formula from the Jan column
across and for it to reference the Feb heading on the summary page but pick
up the detail on the Feb worsheet tab. A formula in the summary sheet such as

=(cellC5,J15) would reference c5 in the summary page which would
have text of 'Feb05' in it and then cell J5 which would be on the worksheet
Feb 05.

Hope this makes sense. Any help would be appreciated.

Regards

Rich

 

[Bernard Liengme]

Follow this method to reference a cell on another worksheet (even in another
open file)
Type =
Open the source worksheet and click on the cell to get something like
=Sheet2!A1 or ='Jan Sales'!A10
Not the single quotes when the sheet name has a space in it.
best wishes

 

[Guest]

Thanks for tis, unfortunately it doesn't answer my question.

What I'd like to do is instead of refernecing the worksheet is to reference
a cell in the summary sheet that has the same text as the worksheet name and
have this then pick up a specific cell from the other worksheet.

Sorry if I'm not explaning my self very clearly.

Regards

Richard

 

[Bernard Liengme]

Sorry, I misunderstood.
In A1 I have the text - Jan Sales
In B1 I have the text - C7
In A2 I have the formula =INDIRECT("'"&A1&"'!"&B1) which displays the value
45 which is what is in cell C7 of the sheet called Jan Sales
In C1 I have the number 7, in A2 I have the formula
=INDIRECT("'"&A1&"'!C"&C1)
which again displays 45.

Let look more closely at first formula: INDIRECT( double-quote single-quote
double-quote ampersand A1 ampersand double-quote single quote
exclamation-mark double-quote ampersand B1)

Second formula: INDIRECT( double-quote single-quote double-quote ampersand
A1 ampersand double-quote single quote exclamation-mark C double-quote
ampersand C1)

best wishes