Using Text from one cell as part of a cell reference in another

[zach]

I have a workbook with precisely one hojillion budget tabs. We have a
summary sheet, but it's kind of clunky and hard to maintain, and I'm
trying to do a replacement.

Since we're constantly adding tabs, a great way to make sure we don't
miss any is to use a macro to list all the tabs in the workbook. So
far, so good. However, I'd like to use that list as the basis for the
summary, so it's all basically automatic.

For instance, lets say A1 through A3 has

Alison
Bob
Carl

These are the names of the tabs that the macro pulled.

I'd like B1 through B3 pull a specific cell from that sheet, let's say
cell Z1.

So the simple version of the formula for B1 would be =Alison!Z1

But I'd like a formula I can just copy down that reads more like
=[A1CellValue]!Z1

Is there any simple way to do this?

 

[L. Howard Kittle]

=A1&Z1

or

=$A$1&Z1

If I understand the question..?

HTH
Regards,
Howard

 

[zach]

Thanks. Actually, the first one seems to be working for tabs with
spaces in it too. But, thanks! This solved my problem.

Hi,

Small correction in case some of your sheet names includes spaces:

=INDIRECT("'"&A1&"'!Z1")

HTH
Jean-Guy

Hi,

Try:

=INDIRECT(A1&"!Z1")

HTH
Jean-Guy

I have a workbook with precisely one hojillion budget tabs. We have a
summary sheet, but it's kind of clunky and hard to maintain, and I'm
trying to do a replacement.
Since we're constantly adding tabs, a great way to make sure we don't
miss any is to use a macro to list all the tabs in the workbook. So
far, so good. However, I'd like to use that list as the basis for the
summary, so it's all basically automatic.
For instance, lets say A1 through A3 has
Alison
Bob
Carl
These are the names of the tabs that the macro pulled.
I'd like B1 through B3 pull a specific cell from that sheet, let's say
cell Z1.
So the simple version of the formula for B1 would be =Alison!Z1
But I'd like a formula I can just copy down that reads more like
=[A1CellValue]!Z1
Is there any simple way to do this?