This is about using wildcards in referencing cells on other worksheets.
If you know the basic rule about naming a worksheet, you should know that we cannot use ? or * in any part of a worksheet’s name. So there won’t be a worksheet named ‘???’ and intuitively you will think the formula set above gives you nothing but error. Well, it depends… on how you name worksheets in your workbook.
Here’s a typical example of 3D SUM:
The most common way to add the content of C3 from all worksheets should be using mouse to point to the cell reference on different sheets one by one, with the + operator. You can imagine how inefficient it could be.
As the targeted sheets are setting next to each other, there is a more convenient way to do so, as described HERE.
If you prefer to stick to your keyboard, you will love this trick:
=SUM(‘???’!C3) is what I input to the cell
This is what I got:
You may think that keying in the required string “Jan:Dec!” directly, or using mouse to select all the worksheets involved (as described HERE) is equally simple and efficient. Yes it is when all the targeted worksheets are setting next to each other.
Think about a situation like this:
What you need to do is to add all actual numbers and forecast numbers in a summary table on the worksheet ‘Full Year’. There is no way for you to input the sheet reference easily. To tackle this, let’s try the followings:
In C3 of ‘Full Year’, input =SUM(‘???’!C3)
This is what I got:
See! Excel realizes that the sheets are not adjacent to each other and it won’t give you ‘Jan:Dec’! this time.
How about to sum data on C3 from other sheets starting with “f_”?
In D3 of ‘Full Year’, input =SUM(‘f_*’!C3)
This is what I got:
Isn’t it magical? Excel automatically transforms the short formula I input to what I need.
Here is a Sample File for you to follow the steps above. (yes, I know how to put an excel file here finally. ) 🙂
The expression
- ‘???’! means any worksheets named with three characters, except active sheet. It includes worksheet named as ‘ABC’, if any. ? is the wildcard meaning “Any single character”.
- ‘f_*’! means any worksheets named start with “f_”, except active sheet. It includes worksheet named as ‘f_ABCDEFG’, if any. * is the wildcard meaning “Any string of character(s)”.
Notes: When you use wildcard to reference to other sheets, Excel will also look into hidden sheets and included them in your formula. Please pay attention to this.
wmfexcel 
Pingback: #Excel Super Links #35 – shared by David Hager | Excel For You
Very good and time saving tip. Thanks for enlightening us!!!
LikeLike
You are welcome 😀
LikeLike
Hi thank you so much for sharing such wonderful trick.
I am sorry for bothering you but I have 1 question…
My summary is sheet is in Sheet3.
When I used this trick in Sheet3,
=SUM(‘*’!A1)
it just does =SUM(sheet1:sheet2!A1)
Is there any way to include the cell in the summary sheet too?
Thank you & best regards,
LikeLike
You are welcome!
LikeLike
btw, for your question the way to input the 3D sum described will ignore active sheet.
but you may do a simple twist to achieve what you want:
=SUM(‘‘!A1)+A1
or
=SUM(‘‘!A1,A1)
Hope this helps.
LikeLiked by 1 person
Thank you so much for confirming that it ignores active sheet! I really enjoy reading your blog. It really helps improve my productivity!!!! Thank you so much!!!!!
LikeLike
Glad you like it! I improve my skills a lot in the process too. 😀
LikeLike
This is ******* awesome 🙂
I hope Ii wrote enough * :p
Thanks MF
LikeLike
You are welcome, Ron.
Glad you like it !
LikeLike
Pingback: Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition | Chandoo.org - Learn Microsoft Excel Online
Super Stuff there! I knew wildcards but never thought of using them in the SUM function and on multiple sheets!
LikeLike
Thanks. Glad you like it.
LikeLike
Whoever discovered it, I don’t care! That’s astonishing! 🙂
LikeLike
Hi XOR LX
Nice to hear from you again!
Agree with you
Sharing useful finding could be equally important to the finding itself.
Cheers,
LikeLike
Indeed!
Just a bit of a shame that we can’t generate such constructions via INDIRECT, just as we can’t with constructions such as:
=SUM(Jan:Feb!A1)
I was half hoping we would be able to, but alas no. Still a wonderful find, though!
Cheers
LikeLike
Exactly. The trick is only good for a static workbook. In case extra worksheets are needed in the future, the formula won’t work and we have to be careful about that.
But still, it’s a good hidden feature I believe, although it’s imperfect.
What functions/features are perfect btw. 😛
Cheers,
LikeLike
indeed, very informative…thank you!
LikeLike
Thanks Dennis 😀
LikeLike
It’s awesome sir…….. really a great way to do sum….
But i have one query……m suppose we have a combination of worksheets on which some some sheets have 3 character name and some sheets have 4 character name then in such case how to do the sum of all sheets together?????
Thanks for the informative post.
Anup
LikeLike
Thanks Anup for your kind words. For your case, simply use a single * would do.
Hope it help. Cheers 😀
LikeLike
Well, folks, if you got my book, This Isn’t Excel, it’s Magic!, then you’d see this tip #85 (page 166) already “discovered”. The book has 110 other tips & tricks mostly not seen anywhere else! – You can get it by typing “Umlas Magic” into Amazon.com.
Bob Umlas, Excel MVP
LikeLike
Hi Bob,
Thanks. That’s a very temptation to read your book then.
Cheers,
LikeLike
Awesome!
I think I know Excel and then it smacks me this
LikeLike
Thanks 😁
LikeLike
Incredible discovery, MF!
LikeLike
Thank you Mynda indeed! 😀
LikeLike
Wow… Never knew that. Awesome info.
LikeLiked by 1 person
wow… so glad that Chandoo left a comment here! 🙂
LikeLike
I’m speechless.
+1M
LikeLike
Thanks! 😀
LikeLike