INDIRECT function with tab names consisting of several words

[minsk2000]

Hi Everyone,

I used to use INDIRECT function to get amounts from various sheets into the Summary sheet.
If there was sheet with the name Cleveland and in cell G10 there was amount of $10000.
In the Summary sheet I had list of names in range A1:A10 so to get amount of $10000 into summary tab I used formula INDIRECT(A1&”!G10”).
So the amount from of 10000 appeared in the Summary sheet.

Everything worked fine till I started using this formula with the sheet names that consisted of several words. Say sheet name “Sales by State”. If I use the same INDERCT function with this name #REF! appears in the summary sheet.
Unfortunately I cannot change sheet names to create single word name.

Is there any way to indirectly reference from sheets consisting of multiple words.

Thanks very much

Minsk

 

[Domenic]

Try...

=INDIRECT("'"&A1&"'!G10”)

Hope this helps!