Creating Variable Dependent Dynamic Print Ranges

Today's topic is a bit more advanced, but will wrap up my print setup series. (Disclaimer: I did not create this technique nor most others I post, they are just bits of information I've picked up over the years and reformulated into solutions for my problems). Warning - this will get to be a bit lengthy...

I'm going to discuss how to create a dynamic print range that is dependent on values you are using in your worksheet.

What??

Yes, it's a somewhat obscure, and rarely necessary technique, but I've needed it before, and I think there are those out there that will find this useful.

Scenario: You're working on a workbook that is designed to calculate fixed-rate mortage payments, and you've got an amortization table you would like to be able to print out. Unless you are using a macro to fill your formulas to the appropriate row based on the repayment terms, chances are your technique is to take the formulas, copy them down a couple hundred rows, and then just ignore the cells that aren't used. The problem with this is if you need to print the workbook, you will either print a lot of uncessary white space, or you will need to define your print area using the manual print area setup. This is not really practical if you are running numerous scenarios often. Instead, with my technique, depending on your term, you will be able to have the print range set itself to exactly where you need it.

Solution: We are going to use two features of excel to set your workbook up for this. We will define a named range, and we will use the offset function. I'll describe each individually, then show how we can use this for our purposes:

Define Named Range:

One of the nice features in excel is the ability to assign a name to a specific range. This range can be a single cell, or an area of cells. You could name a cell "TaxRate", or you could name an series of cells "TaxRateTable" all at once. Then, you can use these named ranges in formulas, as opposed to using the standard cell reference. For Example, if cell A1 were named "Dollars", you could write a formula in one of two ways:

A1 + B1 = C1 or

Dollars + B1 = C1

To name a range, go to Insert -> Name -> Define

You can type the name of your range in the top section, and then select your range in the bottom. click in the bottom box, and then you can click and drag on the spreadsheet to fill in the range. Alternatively, you can type the range in. Then press add, and it will be added to the list, and part of your workbook until you delete it.

Once you've named a range - you can always reference that range in a formula, and it will refer to the cells you've selected.

Print Area: When you set the print area (see prior post), you will notice that a named range is created called Print_Area. This is how excel keeps track of the area you've set, and so it knows what to print.

Offset Formula:

The offset formula allows you to reference a cell, starting from one cell, and telling it how many rows and columns to move away.

The formula is =offset(Reference, rows, columns)

Reference refers to the starting location. For example, A1. Rows refers to how many rows to move down (- moves up), and Columns refers to how many columns to move right (- moves left).

The result of the formula can be either the value of the cell, or the actual "range" of the cell, depending on the manner in which it is used.

Combining these techniques to define a dynamic Print Area:

You will notice when you define a named range, the box you define the range uses a formula. In this case, using the offset formula will allow us to refer to the range the offset formula gives us.

In this example, assume that the number of rows DOWN you want the print area to be is equal to the number in cell B1.

Print_Area =$A$1:Offset($A$1,$B$1,0)

So, the starting point in offset is A1, we are going to move down the number in B1, and we will move across 0 rows. If the number 10 is in B1, The print area will be set to A1:A11 (A1 + 10).

Here's the formula in the define named range box:

(You will notice it adds your sheet name automatically). If you go to print preview, you will notice the first 11 rows are included. If you change the value in B1, the print range will change.

Back to the mortgage example: I set my offset starting range to the 1st period in my payment term schedule. Then for the offset rows, I link it to the payment terms. Thus, if I have a 360 month mortgage, the print area extends 360 rows. If I have a 180 month mortgage, the print area extends 180 rows.