INDIRECT function with tab names consisting of several words

minsk2000

Board Regular
Joined
Dec 25, 2005
Messages
68
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,311
Members
409,862
Latest member
lbisacca
Top