A 3-D Reference (also known as a 'Spearing Formula') is when you need to shoot through a series of worksheets to add up the same cell on many worksheets. Episode #1253 shows you how to create these references in just a few clicks!
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel. For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel. For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Excel In Depth, chapter 17, 3D References.
Hey, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
In chapter 17 we talk a lot about, a lot about cool formula tricks.
The topic that I chose for the podcast though is what some people call a Spearing Formula or a 3D Reference.
All right so you see, we have sheets for January, February, March, April and so on, and I want to total all of those sheets up.
We should have sheets going out, I think to December.
Yes, so I'm gonna add a new sheet here before January, and I'm gonna copy the format and structure of the January sheet.
I'm gonna do that by holding down the Ctrl key, and then dragging January to the left.
So, there's my new sheet, I'm gonna call that total and then here where I want to add up January, and I want cell B4 from January, February, March, April.
I mean it would be tempting to actually write a formula of equal January B4 plus February B4 plus March B4 and so on and that would work.
But, a different way to go is to say equal sum open parenthesis, and then the first sheet name January, colon, the last sheet name December exclamation point B4.
That's it, it adds up all the numbers.
Now that we have that formula, we can actually copy that formula, that may use Paste Special Formulas to all of the cells that need to add things up.
So I'm using Paste Special Formulas there, and so now I built a summary sheet that Spears through cell B4 of every single sheet from January through December, and adds all of those up so a very fast fast way to go.
3D Reference or a Spearing Formula.
I wanna thank for stopping by.
See you next time with another netcast from MrExcel.
Excel In Depth, chapter 17, 3D References.
Hey, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
In chapter 17 we talk a lot about, a lot about cool formula tricks.
The topic that I chose for the podcast though is what some people call a Spearing Formula or a 3D Reference.
All right so you see, we have sheets for January, February, March, April and so on, and I want to total all of those sheets up.
We should have sheets going out, I think to December.
Yes, so I'm gonna add a new sheet here before January, and I'm gonna copy the format and structure of the January sheet.
I'm gonna do that by holding down the Ctrl key, and then dragging January to the left.
So, there's my new sheet, I'm gonna call that total and then here where I want to add up January, and I want cell B4 from January, February, March, April.
I mean it would be tempting to actually write a formula of equal January B4 plus February B4 plus March B4 and so on and that would work.
But, a different way to go is to say equal sum open parenthesis, and then the first sheet name January, colon, the last sheet name December exclamation point B4.
That's it, it adds up all the numbers.
Now that we have that formula, we can actually copy that formula, that may use Paste Special Formulas to all of the cells that need to add things up.
So I'm using Paste Special Formulas there, and so now I built a summary sheet that Spears through cell B4 of every single sheet from January through December, and adds all of those up so a very fast fast way to go.
3D Reference or a Spearing Formula.
I wanna thank for stopping by.
See you next time with another netcast from MrExcel.
