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!!!

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