Pablo sends in a cool tip for using Spearing Formulas. Episode 1099 shows you how.
This blog is the video 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 blog is the video 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.
How we gonna analyze this. Well, let's fire up a pivot table and see you can solve this problem.
Hey all right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is our 999 episode.
Now, cool today we have a email from Pablo.
Pablo is talking about when you have a formula that adds up the same cell on multiple sheets, Spearing formula as we have a formula here equal SUM region 1 colon region 3 the exclamation point or what the program is called a bang and then C5 and Pablo say's here the problem with that is if you would add a new region later. So, for example, let me take region 2 and just control drag it over here to become region 4 that formula does not automatically expand.
So, we come back here to the problem sheet you see that still goes region 1 to region 3.
So, Pablo's tip today is he created a couple of interesting worksheets here.
He created worksheet called first and worksheet called last.
You see that he just changed the fill on that entire worksheet to be blue and got rid of the grid line.
So, people are where they're not supposed to do anything there, and then in his summary sheet instead of going from region 1 colon region 3 he goes colon first colon Last bang C5 and that works perfectly we can add new sheets.
We can delete sheets as long as we add them between first and last everything is cool.
So, hey a great tip from Pablo. I will send Pablo an Excel master pin for that one.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well. thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amounts of data.
How we gonna analyze this. Well, let's fire up a pivot table and see you can solve this problem.
Hey all right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is our 999 episode.
Now, cool today we have a email from Pablo.
Pablo is talking about when you have a formula that adds up the same cell on multiple sheets, Spearing formula as we have a formula here equal SUM region 1 colon region 3 the exclamation point or what the program is called a bang and then C5 and Pablo say's here the problem with that is if you would add a new region later. So, for example, let me take region 2 and just control drag it over here to become region 4 that formula does not automatically expand.
So, we come back here to the problem sheet you see that still goes region 1 to region 3.
So, Pablo's tip today is he created a couple of interesting worksheets here.
He created worksheet called first and worksheet called last.
You see that he just changed the fill on that entire worksheet to be blue and got rid of the grid line.
So, people are where they're not supposed to do anything there, and then in his summary sheet instead of going from region 1 colon region 3 he goes colon first colon Last bang C5 and that works perfectly we can add new sheets.
We can delete sheets as long as we add them between first and last everything is cool.
So, hey a great tip from Pablo. I will send Pablo an Excel master pin for that one.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well. thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
