Automating references to multiple worksheets

[Rick Gregory]

[Excel 98/OS9]

I have an Excel workbook that contains multiple worksheets. Each worksheet
is identical in structure, but contains different data (each sheet
represents a different person).

I am trying to create a table in a new sheet that pulls selected bits from
each other sheet. For example, column 1 might contain the person's name
(which is same cell in every sheet). Column 2 would have the person's phone
number, etc.

I know I can manually type a function such as =sheet1!B7 to pull from
another sheet. But can I create a function that automatically picks up each
sheet name (30+ sheets)?

One idea is to type the sheet names in a column, and then somehow reference
those to create the function... For example, if cell "a1" contains the text
"Sheet1", can I create a formula that will pull the name from a1 to yield a
functioning formula (=sheet1!B7)...? Make sense?

I hope this is somewhat clear. Any help is greatly appreciated!!!

 

[Bernard Rey]

Rick Gregory wrote :

I have an Excel workbook that contains multiple worksheets. Each worksheet
is identical in structure, but contains different data (each sheet
represents a different person).

I am trying to create a table in a new sheet that pulls selected bits from
each other sheet. For example, column 1 might contain the person's name
(which is same cell in every sheet). Column 2 would have the person's phone
number, etc.

I know I can manually type a function such as =sheet1!B7 to pull from
another sheet. But can I create a function that automatically picks up each
sheet name (30+ sheets)?

One idea is to type the sheet names in a column, and then somehow reference
those to create the function... For example, if cell "a1" contains the text
"Sheet1", can I create a formula that will pull the name from a1 to yield a
functioning formula (=sheet1!B7)...? Make sense?

The "indirect" function does exactly that: if you type =INDIRECT(A1&"!B7")
in the cell, it'll do exactly what you describe. There could be different
solutions, but this one is probably a simple one.

It would of course run not only in Excel 98, but also in any other Mac or PC
version.

 

[Rick Gregory]

Okay... That worked perfectly. Now I'm trying to figure out how to make it
work across multiple workbooks.

For example, in my main sheet, I now have a column with the workbook file
name, and another column with the worksheet names (each wb has multiple
sheets). I can't seem to get the syntax right to use INDIRECT to reference
an external workbook/worksheet combination.

Say my workbook name is in a2, worksheet name is in b2, and I want to pull
cell C15 from that sheet... what would the formula be??

Thanks in advance.

 

[JE McGimpsey]

Okay... That worked perfectly. Now I'm trying to figure out how to make it
work across multiple workbooks.

For example, in my main sheet, I now have a column with the workbook file
name, and another column with the worksheet names (each wb has multiple
sheets). I can't seem to get the syntax right to use INDIRECT to reference
an external workbook/worksheet combination.

Say my workbook name is in a2, worksheet name is in b2, and I want to pull
cell C15 from that sheet... what would the formula be??

One way:

=INDIRECT("[" & A2 & "]" & B5 & "!C15")

Note that with INDIRECT, the external workbook needs to be open or
you'll get a #REF! error.