A 3-D Reference is when you need to shoot through the worksheets to add up the same cell on many worksheets. See how to create these references in this video
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 17 – 3D references!
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
In chapter 17 we talked 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.
You see, here 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, yep.
So, I'm going to add a new sheet here before January, and I'm going to copy the format and structure of the January sheet.
I'm going to do that by holding down the Ctrl key, and then dragging January to the left.
So there's my new sheet, I’m going to 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 =Jan!B4+Feb!B4+Mar!B4 and so on, and that would work.
But a different way to go is to say =SUM( , and then the first sheet name, January: the last sheet name, December!B4, and it adds up all the numbers.
Now that we have that formula, we can actually copy that formula, you just 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-December, and adds all of those up.
So a very fast way to go… (unclear) Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Excel In Depth chapter 17 – 3D references!
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
In chapter 17 we talked 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.
You see, here 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, yep.
So, I'm going to add a new sheet here before January, and I'm going to copy the format and structure of the January sheet.
I'm going to do that by holding down the Ctrl key, and then dragging January to the left.
So there's my new sheet, I’m going to 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 =Jan!B4+Feb!B4+Mar!B4 and so on, and that would work.
But a different way to go is to say =SUM( , and then the first sheet name, January: the last sheet name, December!B4, and it adds up all the numbers.
Now that we have that formula, we can actually copy that formula, you just 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-December, and adds all of those up.
So a very fast way to go… (unclear) Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
