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

Z

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?
 
Z

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

pinmaster said:
Hi,
Try:
Click to expand...
=INDIRECT(A1&"!Z1")
Click to expand...
HTH
Jean-Guy
Click to expand...

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Include a value from one cell within text of another cell. 2
Formula with dynamic tab reference 4
Manage a high number of worksheets in one workbook (Excel 2007) 4
Using cell as worksheet reference (macro) 1
Using text from cell in a range lookup 1
Return cell reference as text 1
Macro that uses a cell value to copy another cell 3
copy selected part number of text from one cell into another cell 1

Top