MRAM asks if there is a way to Total All Pages so far...at the bottom of each Printed Page. MRAM is struggling with the situation where someone Inserts or Deletes Rows and then the Subtotal needs to move to a new location. In Episode #1668 today, Bill shows us a Formula-based solution [Tomorrow, Bill will show us a VBA Macro solution].
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode: 1668, Subtotal in Footer.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's questions sent in, so it's the same Patrick's Day weekend, right?
And MRAM sent this question.
He says, "Hey, I want to put a subtotal of the totals so far in the footer of each page." And it's a real [ inaudible at 0:25 ]. It's a...
No way, it'll never happen forget it, but then he kept asking questions, asking questions and since so here we are Monday morning, and I think I have two different ways to get a subtotal in the footer.
And what MRAM said is it's really frustrating for the insert new rows, or delete new rows.
The subtotal needs to move.
Alright, so I have two ways to do this.
I'm going to do one way today, that's a formula way.
And tomorrow, we'll try a VBA macro way.
First thing we do, you wanna make sure that you get your page layout set up, so that you'll never change.
Choose the margins that you want.
Make sure to set up the Print Title.
So, here I have rows 1 to 5, repeating at the top of each page.
You know, get everything exactly like you want it, and then take a look at Print preview.
So, I just did [ CTRL+P ].
Excel 2010 and newer you can just do to [ CTRL+P ] and you get Print Preview over here, and it looks like the invoices run from 1 to 40.
And I know that none have been deleted here.
So, there's 40 rows on the first page and then from 41 to 80 on the second page.
Reliably, I'm getting 40 rows on every page.
Now, if we change the row height later, this whole thing's gonna fall apart.
Right now, I can count on 40 rows in each page.
So, let's talk about let's build this formula one step at a time.
Equal row will tell me the row number of the current row.
All right, so six, seven, eight, nine, ten.
And think about division, when you were learning division.
Way back in third or fourth or second grade, whenever you learn division and initially when you would do division, you would often express the answer as a number with a remainder.
All right, so if I asked for 45 divided by 40, the answer be 1 remainder 5.
1 remainder 5.
And what I need to do here is, I need to find the rows where the remainder of this number divided by 40 is equal to 5.
Okay, because that will be the last row on each page.
So, there is a great function in Excel that will let us find the remainder and that function is called MOD.
So, I want the MOD of this row number divided by 40 and that's going to give me the remainder to the division, so let's go back down to our row 45.
So, you can see. So, here's row 45, 45 divided by 40.
The remainder is 5.
MOD doesn't bother to tell you what the the first number was.
It only tells you what the Remainder was.
By the way if you need that first number, I think you use QUOTIENT.
So, QUOTIENT and MOD will give you the, the answer and the remainder.
All right, so now, I can tell whether I'm on the last row of the page when this is equal to 5.
All right, so from there, we say equal if.
This whole thing is equal to 5.
Then I want to put a total and the total is going to be the sum.
Always wanna go from E6.
So, [ E$6 ], down to the current row, which in this case is still E6.
It's always confusing, in the first cell there otherwise show nothing at all.
All right, and I'll press [ CTRL+ENTER ] there, and they give me the warning that hey, I'm ignoring adjacent cells.
But that's okay in this particular.
So, at the end of page 1, we have 96 thousand.
I scroll down to the bottom of page to 2, 191100 and it should continue to grow.
Now, maybe people won't know what that is.
So, we should put a heading that says, "Total All Pages So Far" or something like that.
Okay now, what if we just wanted a total of the current page?
So, not total of all pages so far but total of this page?
So, this function, the SUM function is going to have to grab a variable range that grabs the last 40 rows.
And for that I'm going to use the offset function, so we're going to start from the current cell that's F6.
I'm gonna go minus 39 rows up, minus one column over and grab a range that's 40 rows tall by 1 column line.
Close the offset and double click to shoot that down.
Page down, 96,000.
Now, here's the big test.
So, if we insert three new rows and we'll just add a thousand to each of those, our total, they're moved.
It's still I'm appearing on row 45, thanks to our MOD function and it's grabbing the last 40 cells.
So, even if we insert rows, delete rows, the total is going to appear to move to the correct place.
And we will get a total at the bottom of each page.
All right, so that's my formula based solution for MRAM.
Tomorrow, we will try a VBA solution.
Oh, hey, I wanna thank every one for stopping by.
And wanna thank MRAM for sending that questioned in.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode: 1668, Subtotal in Footer.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's questions sent in, so it's the same Patrick's Day weekend, right?
And MRAM sent this question.
He says, "Hey, I want to put a subtotal of the totals so far in the footer of each page." And it's a real [ inaudible at 0:25 ]. It's a...
No way, it'll never happen forget it, but then he kept asking questions, asking questions and since so here we are Monday morning, and I think I have two different ways to get a subtotal in the footer.
And what MRAM said is it's really frustrating for the insert new rows, or delete new rows.
The subtotal needs to move.
Alright, so I have two ways to do this.
I'm going to do one way today, that's a formula way.
And tomorrow, we'll try a VBA macro way.
First thing we do, you wanna make sure that you get your page layout set up, so that you'll never change.
Choose the margins that you want.
Make sure to set up the Print Title.
So, here I have rows 1 to 5, repeating at the top of each page.
You know, get everything exactly like you want it, and then take a look at Print preview.
So, I just did [ CTRL+P ].
Excel 2010 and newer you can just do to [ CTRL+P ] and you get Print Preview over here, and it looks like the invoices run from 1 to 40.
And I know that none have been deleted here.
So, there's 40 rows on the first page and then from 41 to 80 on the second page.
Reliably, I'm getting 40 rows on every page.
Now, if we change the row height later, this whole thing's gonna fall apart.
Right now, I can count on 40 rows in each page.
So, let's talk about let's build this formula one step at a time.
Equal row will tell me the row number of the current row.
All right, so six, seven, eight, nine, ten.
And think about division, when you were learning division.
Way back in third or fourth or second grade, whenever you learn division and initially when you would do division, you would often express the answer as a number with a remainder.
All right, so if I asked for 45 divided by 40, the answer be 1 remainder 5.
1 remainder 5.
And what I need to do here is, I need to find the rows where the remainder of this number divided by 40 is equal to 5.
Okay, because that will be the last row on each page.
So, there is a great function in Excel that will let us find the remainder and that function is called MOD.
So, I want the MOD of this row number divided by 40 and that's going to give me the remainder to the division, so let's go back down to our row 45.
So, you can see. So, here's row 45, 45 divided by 40.
The remainder is 5.
MOD doesn't bother to tell you what the the first number was.
It only tells you what the Remainder was.
By the way if you need that first number, I think you use QUOTIENT.
So, QUOTIENT and MOD will give you the, the answer and the remainder.
All right, so now, I can tell whether I'm on the last row of the page when this is equal to 5.
All right, so from there, we say equal if.
This whole thing is equal to 5.
Then I want to put a total and the total is going to be the sum.
Always wanna go from E6.
So, [ E$6 ], down to the current row, which in this case is still E6.
It's always confusing, in the first cell there otherwise show nothing at all.
All right, and I'll press [ CTRL+ENTER ] there, and they give me the warning that hey, I'm ignoring adjacent cells.
But that's okay in this particular.
So, at the end of page 1, we have 96 thousand.
I scroll down to the bottom of page to 2, 191100 and it should continue to grow.
Now, maybe people won't know what that is.
So, we should put a heading that says, "Total All Pages So Far" or something like that.
Okay now, what if we just wanted a total of the current page?
So, not total of all pages so far but total of this page?
So, this function, the SUM function is going to have to grab a variable range that grabs the last 40 rows.
And for that I'm going to use the offset function, so we're going to start from the current cell that's F6.
I'm gonna go minus 39 rows up, minus one column over and grab a range that's 40 rows tall by 1 column line.
Close the offset and double click to shoot that down.
Page down, 96,000.
Now, here's the big test.
So, if we insert three new rows and we'll just add a thousand to each of those, our total, they're moved.
It's still I'm appearing on row 45, thanks to our MOD function and it's grabbing the last 40 cells.
So, even if we insert rows, delete rows, the total is going to appear to move to the correct place.
And we will get a total at the bottom of each page.
All right, so that's my formula based solution for MRAM.
Tomorrow, we will try a VBA solution.
Oh, hey, I wanna thank every one for stopping by.
And wanna thank MRAM for sending that questioned in.
See you next time for another netcast from MrExcel.
