Build a sheet name in an Excel formula

[Keith Howie]

I have an Excel 2007 workbook with a summary page and a number of other sheets named sequentially as in Spend(1), Spend(2), Spend(3), etc. On the summary sheet, I have a series of numbers, one per row, that correspond with the sheet numbers. (1, 2, 3, etc.)
I am trying to build a formula that would exist on the summary sheet that would return the results of cell G14 from the applicable sheet. (for the number 4, Spend(4), cell G14 or for the number 8, the results from sheet Spend(8), cell G14.
I have tried to build it as follows:
=?Spend(?&A19&?)!)&G14 (where A19 contains the reference number)
I also tried =Indirect(?Spend(?&A19&?)!?&G14)
Both of these return me a #REF error. Can you help me get this right?



Submitted via EggHeadCafe - Software Developer Portal of Choice
Easy ROT39 Encryption / Decryption Class
http://www.eggheadcafe.com/tutorial...0b-4e2827bfb017/easy-rot39-encryption--d.aspx

 

[Pete_UK]

Try this:

=INDIRECT("'Spend("&A19&")'!G14")

Hope this helps.

Pete

 

[Dave Peterson]

Check your other post.

I have an Excel 2007 workbook with a summary page and a number of other sheets named sequentially as in Spend(1), Spend(2), Spend(3), etc. On the summary sheet, I have a series of numbers, one per row, that correspond with the sheet numbers. (1, 2, 3, etc.)
I am trying to build a formula that would exist on the summary sheet that would return the results of cell G14 from the applicable sheet. (for the number 4, Spend(4), cell G14 or for the number 8, the results from sheet Spend(8), cell G14.
I have tried to build it as follows:
=?Spend(?&A19&?)!)&G14 (where A19 contains the reference number)
I also tried =Indirect(?Spend(?&A19&?)!?&G14)
Both of these return me a #REF error. Can you help me get this right?

Submitted via EggHeadCafe - Software Developer Portal of Choice
Easy ROT39 Encryption / Decryption Class
http://www.eggheadcafe.com/tutorial...0b-4e2827bfb017/easy-rot39-encryption--d.aspx