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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,144,699
Messages
5,725,824
Members
422,644
Latest member
BethGS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top