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.