# Solved: How to use the indirect function with worksheets names that have spaces

### Problem Symptom

I have set up a Sumifs formula with an indirect and it works perfectly when the worksheet names and other entries have no spaces. However, when I want to test it with entries where worksheets have spaces in the names and there is something I am missing in the delightful configuration of apostrophes etc.

This is the formula that works:

``=SUMIFS(INDIRECT(C\$3&"!B:B"), INDIRECT(C\$3&"!A:A"),\$A4)``

This is the formula that doesn’t work:

``=SUMIFS(INDIRECT("'"&B\$3&" '!"&B:B), INDIRECT("'"&B\$3&"'!"&A:A),\$A4)``

### Solution

The sheet name for Jan num has a space at the beginning as well.

So needs to be:

``=SUMIFS(INDIRECT("' "&B\$3&"'!B:B"),INDIRECT("' "&B\$3&"'!A:A"),\$A4)``

Or delete the leading space and use:

``=SUMIFS(INDIRECT("'"&B\$3&"'!B:B"),INDIRECT("'"&B\$3&"'!A:A"),\$A4)``

You can use this formula to pull the sheet name from a specific sheet:

``=MID(CELL("filename",' Jan num'!\$A\$1),FIND("]",CELL("filename",' Jan num'!\$A\$1),1)+1,LEN(CELL("filename",' Jan num'!\$A\$1)))``

When setting it up you have to make sure the sheet name referred to in the formula is correct (like in this instance with the leading space on ” Jan num” but once setup if the sheet name changes then the reference will change. #### Ads Blocker Detected!!!

This site depends on revenue from ad impressions to survive. If you find this site valuable, please consider disabling your ad blocker. 