Build a sheet name in an Excel formula
 

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/tutorials...yption--d.aspx

Build a sheet name in an Excel formula
 

Try this:

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

Hope this helps.

Pete

On Dec 9, 1:04*am, Keith Howie wrote:
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 Classhttp://www.eggheadcafe.com/tutorials/aspnet/65efb7c9-5638-4149-a70b-4...

Build a sheet name in an Excel formula
 

Check your other post.

Keith, Howie wrote:

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/tutorials...yption--d.aspx

--

Dave Peterson