Referencing sheets in a workbook

[cwhaley]

Can anyone tell me if it's possible to put workbook sheet names in
cells in another worksheet, and use those for reference purposes?

Suppose, for example, I have worksheets named A, B, C and D. I put
those names in cells in worksheet E. Can I then, say, do a VLOOKUP or
any other reference, by using what's in the cells in E to refer to the
sheet I want to draw information from?

This is unfortunately difficult to explain, but I could have a cell in
sheet E that had the formula "=A!b10". What I want to do is substitute
the "A" before the "!" with a cell's contents. Trial and error
attempts have not worked for me so far.

 

[Bob Greenblatt]

Can anyone tell me if it's possible to put workbook sheet names in
cells in another worksheet, and use those for reference purposes?

Suppose, for example, I have worksheets named A, B, C and D. I put
those names in cells in worksheet E. Can I then, say, do a VLOOKUP or
any other reference, by using what's in the cells in E to refer to the
sheet I want to draw information from?

This is unfortunately difficult to explain, but I could have a cell in
sheet E that had the formula "=A!b10". What I want to do is substitute
the "A" before the "!" with a cell's contents. Trial and error
attempts have not worked for me so far.

Sure. Suppose in the current workbook, A1 contains the name of the source
data workbook, and A2 contains the name of the source data worksheet. Then
the formula =indirect("'["&a1&"]"&A2&"'!a1") will retrieve the data from
cell A1 of the workbook and worksheet listed in cells A1 and A2 of this
workbook. This will do so without generating a link to the source workbook,
which may (or may not) be handy to you.

 

[cwhaley]

That's great... thanks!

I've been using Excel for Mac since version 1.0 (remember when the
whole thing fit on one floppy), and somehow never stumbled across the
INDIRECT function. I'm suddenly seeing dozens of uses for that one.

Thanks again!

....Charles