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

    Your Support Matters...

    We run an independent site that\'s committed to delivering valuable content, but it comes with its challenges. Many of our readers use ad blockers, causing our advertising revenue to decline. Unlike some websites, we haven\'t implemented paywalls to restrict access. Your support can make a significant difference. If you find this website useful and choose to support us, it would greatly secure our future. We appreciate your help. If you\'re currently using an ad blocker, please consider disabling it for our site. Thank you for your understanding and support.