Michael has an Excel workbook for printing statements. When the customer changes, there are a different number of records visible thanks to some fairly complex array formulas. How can he adjust the print range to automatically include extra data? Today's episode sets up a print range that dynamically resizes using the OFFSET function.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1756 - Resize the Print Range as Formulas Change!
Alright, today's questions is sent in by Michael.
Michael has a really great application running here to send out statements to tenants, and it uses a formula here that seems to be directly out of Mike Girvin's "Ctrl+Shift+Enter" book.
And the bottom line is that, as you change the data here, it extracts different records from this worksheet.
So for example, if I CTRL+C here, come back and paste, CTRL+V, we get a different number of records.
And Michael wants the print range to extend when we get more records, but he doesn't want it to be that large when we don't have records.
Now, of course there's formulas here.
Alright, so there's all these formulas returning blank sales all the way down, and if we just allowed Excel to print, it would see those formulas and include those in the print range.
Undo here, and we'll go back.
Alright, so my solution: First thing, we want to do is set the print range.
I don't care to where, it really doesn't matter, I just what I have a print area set, so a set print area, alright.
Next, we're going to come back and edit that, but I want to talk about the OFFSET function.
The OFFSET function is a great way to dynamically resize a range in response to some sort of formula, and so, as I look at his report here, there's nothing in column A, there's a heading for dates, and then a number of dates.
And those dates, of course, are really numeric, and that field is not numeric.
So if I would ask for the equal count of column A, so ignore the currency there, it's telling that there are 10 rows of data.
And let's do a little test here, if we copy Baby Spice and paste, CTRL+V, now it's telling that there are 42 rows of data.
Alright so, that little function right there the count of column A, is going to be very useful in figuring out how large the print range is.
But it's not a matter of it being 42 rows, it's always going to be that answer plus 18, because our headings are starting in row 18.
Alright, so here's how we're going to solve this problem: I want to come back to Formulas, and then Name Manager, alright, and here's real life, folks.
We have a lot of names that have changed to reference errors, I'm going to scroll down and look for the name called Print_Area.
And there are actually two, there's one on the Landlord sheet, and one on the TenantStatement sheet.
I'm on the TenantStatement sheet, so that's the one I want to edit.
We're going to come here, and right now it refers to a specific range, a hardcoded range, which is I guess what you would expect, that's what most print ranges are.
I'm going to change this to be the OFFSET function, and we're going to start from the A1 cell, every single time from the A1 cell.
The next two parameters are how many rows down we want to go, no rows down, and how many columns over to want to go, no columns over.
That's to get to the starting cell.
Alright, so I want to start at A1, I don't want to go any rows from there, but then I want to go 18 high plus the count of TenantStatement, you have to put the worksheet name in here, !$A:$A, , and then how many columns wide, well I'm just going to hardcode that to be 6, because I can see that the report goes A:F there.
Alright so let's cover OFFSET again, you start from a specific cell, OFFSET lets you move down and over to get to the new starting cell, in this case A1 is the right spot.
So ,0,0 and then how tall do you want the range to be, 18 plus the count, and how wide, 6! Alright, we'll accept that, missing parentheses right there, check it again, good, and click Close.
So now, if we do CTRL+P for print, you'll see that they are going all the way down to the end of the print range, return back to the spreadsheet, let's go, was it Nancy Drew, copy Nancy Drew in, CTRL+C come over here, paste that into those two cells, we get less records, and you see the print range has already changed as soon as those formulas are changed.
So it's looking over here, in column A, for those dates.
You know, I couldn't use rent or payment because those are not all filled in all the time.
And so using, you know, finding the column that had nothing else in the column except for the numeric values that I wanted, and then adding 18 to get this count up here.
Of course we can delete that, that was just for illustration.
OK, there you go, a rather complicated solution, but no macros required to have that print range automatically extend using the OFFSET function.
Alright, I want to thank Michael for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1756 - Resize the Print Range as Formulas Change!
Alright, today's questions is sent in by Michael.
Michael has a really great application running here to send out statements to tenants, and it uses a formula here that seems to be directly out of Mike Girvin's "Ctrl+Shift+Enter" book.
And the bottom line is that, as you change the data here, it extracts different records from this worksheet.
So for example, if I CTRL+C here, come back and paste, CTRL+V, we get a different number of records.
And Michael wants the print range to extend when we get more records, but he doesn't want it to be that large when we don't have records.
Now, of course there's formulas here.
Alright, so there's all these formulas returning blank sales all the way down, and if we just allowed Excel to print, it would see those formulas and include those in the print range.
Undo here, and we'll go back.
Alright, so my solution: First thing, we want to do is set the print range.
I don't care to where, it really doesn't matter, I just what I have a print area set, so a set print area, alright.
Next, we're going to come back and edit that, but I want to talk about the OFFSET function.
The OFFSET function is a great way to dynamically resize a range in response to some sort of formula, and so, as I look at his report here, there's nothing in column A, there's a heading for dates, and then a number of dates.
And those dates, of course, are really numeric, and that field is not numeric.
So if I would ask for the equal count of column A, so ignore the currency there, it's telling that there are 10 rows of data.
And let's do a little test here, if we copy Baby Spice and paste, CTRL+V, now it's telling that there are 42 rows of data.
Alright so, that little function right there the count of column A, is going to be very useful in figuring out how large the print range is.
But it's not a matter of it being 42 rows, it's always going to be that answer plus 18, because our headings are starting in row 18.
Alright, so here's how we're going to solve this problem: I want to come back to Formulas, and then Name Manager, alright, and here's real life, folks.
We have a lot of names that have changed to reference errors, I'm going to scroll down and look for the name called Print_Area.
And there are actually two, there's one on the Landlord sheet, and one on the TenantStatement sheet.
I'm on the TenantStatement sheet, so that's the one I want to edit.
We're going to come here, and right now it refers to a specific range, a hardcoded range, which is I guess what you would expect, that's what most print ranges are.
I'm going to change this to be the OFFSET function, and we're going to start from the A1 cell, every single time from the A1 cell.
The next two parameters are how many rows down we want to go, no rows down, and how many columns over to want to go, no columns over.
That's to get to the starting cell.
Alright, so I want to start at A1, I don't want to go any rows from there, but then I want to go 18 high plus the count of TenantStatement, you have to put the worksheet name in here, !$A:$A, , and then how many columns wide, well I'm just going to hardcode that to be 6, because I can see that the report goes A:F there.
Alright so let's cover OFFSET again, you start from a specific cell, OFFSET lets you move down and over to get to the new starting cell, in this case A1 is the right spot.
So ,0,0 and then how tall do you want the range to be, 18 plus the count, and how wide, 6! Alright, we'll accept that, missing parentheses right there, check it again, good, and click Close.
So now, if we do CTRL+P for print, you'll see that they are going all the way down to the end of the print range, return back to the spreadsheet, let's go, was it Nancy Drew, copy Nancy Drew in, CTRL+C come over here, paste that into those two cells, we get less records, and you see the print range has already changed as soon as those formulas are changed.
So it's looking over here, in column A, for those dates.
You know, I couldn't use rent or payment because those are not all filled in all the time.
And so using, you know, finding the column that had nothing else in the column except for the numeric values that I wanted, and then adding 18 to get this count up here.
Of course we can delete that, that was just for illustration.
OK, there you go, a rather complicated solution, but no macros required to have that print range automatically extend using the OFFSET function.
Alright, I want to thank Michael for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
