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.