You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically you start out with massive amounts of data.
So how we're going to analyze as well plus fire up a pivot table.
Lets see if you can solve this problem.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
I've got an amazing trick for you today.
I still can't believe that it works Now let's do a quick bit of review.
You know how to do a spearing formula, right a 3D formula.
You can see there I have worksheets from January through December If I want to add up cell B3 on all of those sheets =SUM(Jan:Dec!B3) Press enter.
and it appears right through those worksheets and adds up January through December, all right.
That's great, provided you have some contiguous worksheets.
Let me show you another section I set up here.
So take a look at these worksheets. Andy ABC Andy sales of product DEF, Andy sales of XYZ and then we start over Bob ABC, Bob DEF, Bob XYZ.
Charlie ABC charlie, all right what if we have to add up all the ABCs?
Well that's going to be a little bit of a pain you would think but check this out!
=SUM('*ABC'!B3) All right now, when I press enter, microsoft is going to rewrite this formula pointing at each individual, ABC.
So you see how I entered it, '*ABC' the bang the B3 and when we go back and look at the formula, look at what it did!
It actually went through and figured out all the sheets that had something ABC.
We could also do this =SUM('Bob*'!B4) and it will grab all three Bob sheets. Absolutely amazing.
Great way to write, very cool 3D spearing formulas that don't touch contiguous worksheets.
hey I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Thanks for stopping by. We'll see you next time for another nectcast from MrExel.
Basically you start out with massive amounts of data.
So how we're going to analyze as well plus fire up a pivot table.
Lets see if you can solve this problem.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
I've got an amazing trick for you today.
I still can't believe that it works Now let's do a quick bit of review.
You know how to do a spearing formula, right a 3D formula.
You can see there I have worksheets from January through December If I want to add up cell B3 on all of those sheets =SUM(Jan:Dec!B3) Press enter.
and it appears right through those worksheets and adds up January through December, all right.
That's great, provided you have some contiguous worksheets.
Let me show you another section I set up here.
So take a look at these worksheets. Andy ABC Andy sales of product DEF, Andy sales of XYZ and then we start over Bob ABC, Bob DEF, Bob XYZ.
Charlie ABC charlie, all right what if we have to add up all the ABCs?
Well that's going to be a little bit of a pain you would think but check this out!
=SUM('*ABC'!B3) All right now, when I press enter, microsoft is going to rewrite this formula pointing at each individual, ABC.
So you see how I entered it, '*ABC' the bang the B3 and when we go back and look at the formula, look at what it did!
It actually went through and figured out all the sheets that had something ABC.
We could also do this =SUM('Bob*'!B4) and it will grab all three Bob sheets. Absolutely amazing.
Great way to write, very cool 3D spearing formulas that don't touch contiguous worksheets.
hey I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Thanks for stopping by. We'll see you next time for another nectcast from MrExel.
