Skip to Content

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 Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. We need money to operate the site, and almost all of it comes from online advertising. Please support us by disabling these ads blocker.

Please disable ad blocker