agwalsh
asked on
using indirect function with worksheets names that have spaces
I have set up a Sumifs formula with an indirect and it works perfectly when my sheet names and other entries have no spaces. However I want to test it with entries where worksheets have spaces in their names and there is something I am missing in the delightful configuration of apostrophes etc. Need another set of eyes on it. 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)
Where there are spaces in a sheet name you have to allow for an apostrophe at either end of the name.
ASKER
@Rob so this should be what instead? INDIRECT("'"&B$3&" '!"&B:B) ? thanks
=SUMIFS(INDIRECT(C$3&"!B:B"), INDIRECT(C$3&"!A:A"),$A4)
That working formula includes the column reference within the quotes.
=SUMIFS(INDIRECT("'"&B$3&" '!"&B:B), INDIRECT("'"&B$3&"'!"&A:A),$A4)
That does not include the column reference in the quotes, that is incorrect.
Does the sheet name have the space on the end?
Does the sheet name quoted in B3 have a space?
That working formula includes the column reference within the quotes.
=SUMIFS(INDIRECT("'"&B$3&" '!"&B:B), INDIRECT("'"&B$3&"'!"&A:A),$A4)
That does not include the column reference in the quotes, that is incorrect.
Does the sheet name have the space on the end?
Does the sheet name quoted in B3 have a space?
ASKER
Here's the file - got dummy data in it combining indirect and sumifs.xlsx - ones with no space names work fine - it's the Jan num ones (yellow) that are not working..thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah, worked BEAUTIFULLY ! thank you!!!
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.
For the sample file you would have to recreate for each of the 12 months and amend the sheet name manually.
=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.
For the sample file you would have to recreate for each of the 12 months and amend the sheet name manually.
ASKER
No I get that - they have to match exactly. It's more that I wanted to illustrate a particular thing. Appreciate that observation though.
See attached with sheet names fed automatically, change a sheet name on the tab, eg change August to just Aug, and the summary will stay correct.
combining-indirect-and-sumifs.xlsx
combining-indirect-and-sumifs.xlsx
ASKER
YUM....beautiful