Using a Text Cell to Reference a Worksheet Name

[Guest]

I have one summary worksheet and approximately 45 data worksheets. Each row
on the summary page needs 8 - 10 cells from each data worksheet. The data
worksheets are identical in format. I add about 4 new data worksheets a
month.

I am tired of the tedious formula entry for each new worksheet, and would
like to have a way where i can enter the name of the new worksheet on the
summary page and all of the formulas on that row use the entered text name to
reference the appropriate worksheet and cell.

Alas -- I have been unable to easily do this. I am still a relatively
junior excel user. Any ideas?

 

[Max]

.. a way where i can enter the name of the new worksheet

on the summary page and all of the formulas on that row
use the entered text name to reference the
appropriate worksheet and cell...

One way would be via using INDIRECT

Perhaps a simple example to lead us in here

In Sheet1,

Suppose we list sheetnames in B1:C1,
eg:Sheet2, Sheet3
and we have the cell refs listed in A2:A3, eg: B2, E2

Then, if we put in B2:
=INDIRECT("'" & B$1 & "'!" & $A2)
and copy B2 across & down to C3 ..

B2:C2 will return the same as the link formulas:
=Sheet2!B2, =Sheet3!B2

B3:C3 will return the same as the link formulas:
=Sheet2!E2, =Sheet3!E2

INDIRECT will resolve the concatenation of the sheetname and cell ref text
strings to return the results from the particular sheet and cell listed in
B1:C1, and in A2:A3. So we could define / change the text strings to suit
the purpose.

---

 

[CLR]

As an Example.....assume your data goes in columns B.....K
in cell A1 enter a sheet name
In B1 put this
=INDIRECT($A1&"!a1")
in C1 put this
=INDIRECT($A1&"!B1")...etc etc to get all 10 cells across Row 1
then in A2 put a new sheet name, and just copy and paste all 10 formulas
down to row 2

hth
Vaya con Dios,
Chuck, CABGx3

 

[cvolkert]

I don't have a lot of time right now,but the indirect formula is wha
you are looking for. You can concatenate the cell reference togethe
so that it will do just what you are looking for. I've gotta go watc
24 now - I'll check back later on - Cha

 

[Guest]

Your reply to Jay really helped me out, but I have an 'add on' to his
original question:
How can I use the INDIRECT formula to return the sum of specific cells in a
data worksheet. My summary worksheet has a cell that requires the sum of the
same 4 cells in each corresponding data worksheet. Any tips on modifying to
make it work?

Thanks much,
M. Moncrief

 

[Guest]

In your summary sheet,

Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc,
with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc
you could place this in B2:
=SUM(INDIRECT("'"&$A2&"'!"&B$1))
then simply copy across and fill down to populate the table

 

[PvZ]

Max,

can you (also) help me out on this one:
I also have a summary sheet, called: Graphs, but consisting of graphs.
For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10
The value: "Sheet100" I would like to choose/vary;
- preferably by selecting a cell with the name of the sheet I want the
graph(s)
made from (e.g. A1=Sheet100; A2=Sheet200 aso.
By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the
data on Sheet200 . . . , or
- by typing the name of a worksheet in a (fixed) cell
(e.g. typing: Sheet200 in Cell A1)
Note: all the worksheets have the same columns (but may vary in number of
rows, although this could be also made the same).
Hope you understand my question !

Regards,

Paul

 

[Roger Govier]

Hi

One way
With a list of your sheet names in A1:A20 of sheet Graphs
Create a named range called GraphRange with a value of
=INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10")

Use GraphRange as the source for your graph data.

Enter the row number of the sheet you want in cell B1, and the source
data for the graph will change accordingly
--
Regards
Roger Govier

Max,

can you (also) help me out on this one:
I also have a summary sheet, called: Graphs, but consisting of graphs.
For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10
The value: "Sheet100" I would like to choose/vary;
- preferably by selecting a cell with the name of the sheet I want the
graph(s)
made from (e.g. A1=Sheet100; A2=Sheet200 aso.
By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the
data on Sheet200 . . . , or
- by typing the name of a worksheet in a (fixed) cell
(e.g. typing: Sheet200 in Cell A1)
Note: all the worksheets have the same columns (but may vary in number of
rows, although this could be also made the same).
Hope you understand my question !

Regards,

Paul

In your summary sheet,

Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc,
with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc
you could place this in B2:
=SUM(INDIRECT("'"&$A2&"'!"&B$1))
then simply copy across and fill down to populate the table