Using a Text Cell to Reference a Worksheet Name

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?

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

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


"Jay L" <Jay [email protected]> wrote in message
news:[email protected]...
> 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?

> .. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jay L" <Jay [email protected]> wrote in message
news:[email protected]...
> 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?