Dynamic Print Range Using the Offset Function |
+1 This Page
|
What is the Offset Function?
You can read about the offset function here - there are several examples and explanations showing how the difference parts of the offset function work. This page is going to show an example of a dynamic print range.
The Formula
The formula is =Offset(Reference,Rows,Cols,Height,Width)
For dynamic printing, in this example, we're only using the pieces that are bold. We're going to use the COUNTA formula for both the Height and Width. The COUNTA formula counts non-blanks.
For dynamic printing, in this example, we're only using the pieces that are bold. We're going to use the COUNTA formula for both the Height and Width. The COUNTA formula counts non-blanks.
Step 1
First you need a spreadsheet with data, we're going to use a very simple data set, so you can follow along (and won't have to spend a lot of time creating data or downloading a workbook). Fill cells of your workbook as shown.
|
Step 2
Push 'Ctrl' and 'F3' so the name manager comes up, and select 'New' (in the upper left had corner).
|
Step 3
Give it the following name: 'Print_Range' (no apostrophes) Enter the following formula: =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A$1:$A$8),COUNTA(Sheet1!$A$1:$G$1)) In this example we're only going to cell A8 and G1, however, you'll want to make this rage large enough so that it will capture all potential data that could be entered. For example if we knew there would never be any more rows, but there might be more columns we might change the COUNTA(Sheet1!$A$1:$G$1) to COUNTA(Sheet1!$A$1:$AB$1). Also, we're starting the formula on Cell A1, but this can be changed to any location in the spreadsheet, or you could replace the the COUNTA function with some other function or cell reference that provides the right height and width. |
Step 4
Check the formula entered to ensure it's working correctly. You can do this by clicking the 'Refers To' button as shown in the picture. You can see if it works by the dancing line, if the line is surrounding the data you want it to then the formula works, if it's not then there is an error in the formula.
You can tell this formula works because it refers to cells going out to G1 and down to A8, but the line is only around the cells that have amounts (and everything in between). |
Download this file to See this Example
|
|
||
Other Files that use the Offset Function for Dynamic Printing Found on this Site
We've used the offset function in some of the templates uploaded on this website. If you would like to download the excel templates and see some more real world uses of this fantastic function please click on the links below. Also, don't forget to learn more about this function here.
|
In this template we used references found in other locations of the spreadsheet to determine the dynamic range. Because there is data in multiple rows that we may not want to print we couldn't use the COUNTA formula. Download the template and push 'Ctrl' and 'F3' to see the formula under 'Print_Range'.
 Loan Amortization Schedule - Dynamic Print |
In this template, we used the same formula as described above, however, we started on a different cell and made some other slight modifications (like adding 1 to the height, to account for the blank row between the table and the header). Download the template and push 'Ctrl' and 'F3' to see the formula under 'Print_Range'.
|
