=SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

This is about using wildcards in referencing cells on other worksheets.

Excel Tips - Using Wildcard in referencing cell on other sheets

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:

Excel Tips - Using Wildcard in referencing cell on other sheets 1

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

Excel Tips - Using Wildcard in referencing cell on other sheets 2

This is what I got:

Excel Tips - Using Wildcard in referencing cell on other sheets 3

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:

Excel Tips - Using Wildcard in referencing cell on other sheets 4

Excel Tips - Using Wildcard in referencing cell on other sheets 5

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)

Excel Tips - Using Wildcard in referencing cell on other sheets 6

This is what I got:

Excel Tips - Using Wildcard in referencing cell on other sheets 7

 

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)

Excel Tips - Using Wildcard in referencing cell on other sheets 8

This is what I got:

Excel Tips - Using Wildcard in referencing cell on other sheets 9

Isn’t it magical?  Excel automatically transforms the short formula I input to what I need.

Excel Tips - Using Wildcard in referencing cell on other sheets 10

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.

Advertisements
This entry was posted in Excel Tips, Formula and tagged , , . Bookmark the permalink.

30 Responses to =SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

  1. Saba says:

    Very good and time saving tip. Thanks for enlightening us!!!

    Like

  2. P says:

    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,

    Like

    • MF says:

      You are welcome!

      Like

    • MF says:

      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.

      Like

      • P says:

        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!!!!!

        Like

  3. Ron says:

    This is ******* awesome 🙂
    I hope Ii wrote enough * :p
    Thanks MF

    Like

  4. Pingback: Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition | Chandoo.org - Learn Microsoft Excel Online

  5. Chandeep says:

    Super Stuff there! I knew wildcards but never thought of using them in the SUM function and on multiple sheets!

    Like

  6. XOR LX says:

    Whoever discovered it, I don’t care! That’s astonishing! 🙂

    Like

    • MF says:

      Hi XOR LX
      Nice to hear from you again!
      Agree with you
      Sharing useful finding could be equally important to the finding itself.
      Cheers,

      Like

      • XOR LX says:

        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

        Like

        • MF says:

          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,

          Like

  7. Dennis says:

    indeed, very informative…thank you!

    Like

  8. Anup gupta says:

    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

    Like

  9. Bob Umlas says:

    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

    Like

  10. Jim says:

    Awesome!
    I think I know Excel and then it smacks me this

    Like

  11. Mynda says:

    Incredible discovery, MF!

    Like

  12. Chandoo says:

    Wow… Never knew that. Awesome info.

    Like

  13. XLarium says:

    I’m speechless.
    +1M

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s