Building a Dynamic Sheet number in a 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?

 

[Dave Peterson]

I'd try:
=INDIRECT("'Spend("&A19&")'!G14")

Sometimes you have to surround the sheet names with apostrophes (').

If you include them and they aren't necessary, then they won't hurt.

But if they're required and you don't include them, you've got big trouble!

ps.

I'm not sure what the formula in A19 is, but if it's just a row number
(adjusted), you may be able to use something like:

=INDIRECT("'Spend("&row()-11&")'!G14")

If the formula is in row 19, then =row()-11 will return 8.

 

[Gary''s Student]

This is actually a really good question:

1. because the tab name contains special character, single quotes are also
needed
2. the G14 must be within the double quotes.

Try:

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