Link to home
Create AccountLog in
Avatar of agwalsh
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)

Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Where there are spaces in a sheet name you have to allow for an apostrophe at either end of the name.
Avatar of agwalsh
agwalsh

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?
Avatar of agwalsh

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of agwalsh

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.
Avatar of agwalsh

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
Avatar of agwalsh

ASKER

YUM....beautiful