Spreadsheet: 156232d1337237074-question-about-forms-linked-to-spreadsheets-and-pivot-tables-offset-and-table
Sheet: Offset

 | XYZ Pte Ltd |  |  |  |  |  | Sometimes, we have a report we need to constantly change the Print Area because of the variable rows/columns.
 | Total Revenue Comparison for Jan 2012 |  |  |  |  |  | The same goes for Pivot Tables & Charts.  The offset formula is the solution to resolve this hassle by making it dynamic~
 |  |  | 2012-01-01 00:00:00 | 2011-12-01 00:00:00 | [NAME] |  | OFFSET Formula
 | Revenue |  |  |  |  |  | OFFSET(reference, rows, cols, [height], [width])
 | A1 |  | 50322 | 54670 | -4348 |  | Reference is to indicate where the Range should begin at ($B$2 in this case)
 | A2 |  | 94457 | 97783 | -3326
 | A3 |  | 95590 | 88351 | 7239 |  | OFFSET(reference, rows, cols, [height], [width])
 | A4 |  | 59628 | 63023 | -3395 |  | This is only if you want to indicate how many rows you want it away from the reference.  I usually put "0".
 | A5 |  | 70523 | 56757 | 13766
 | A6 |  | 68052 | 24374 | 43678 |  | OFFSET(reference, rows, cols, [height], [width])
 | A7 |  | 95368 | 77037 | 18331 |  | This is only if you want to indicate how many columns you want it away from the reference.  I usually put "0".
 | A8 |  | 18717 | 34230 | -15513
 | A9 |  | 25955 | 59983 | -34028 |  | OFFSET(reference, rows, cols, [height], [width])
 | A10 |  | 15269 | 20675 | -5406 |  | This is where we can apply a dynamic range, meaning it will change whenever our data lengthens or shortens horizontally.
 | A11 |  | 64082 | 19448 | 44634 |  | Counting manually from B2 to B18, we have 17 rows to print.
 | A12 |  | 20324 | 21864 | -1540
 |  |  |  |  |  |  | OFFSET(reference, rows, cols, [height], [width])
 |  |  |  |  |  |  | This is where we can apply a dynamic range, meaning it will change whenever our data lengthens or shortens vertically.
 |  |  |  |  |  |  | Counting manually from column B to column F, we have 5 columns to print.
 |  |  |  |  |  |  | With such, the formula would be:
 |  |  |  |  |  |  | #N/A |  | OFFSET($B$2,0,0,17,5)
 |  |  |  |  |  |  | The reason it's showing a formula error is because the result is a range, not a value.  And excel does not show range as
 |  |  |  |  |  |  | a result.  It is actually indicating B2:F18.  To prove it, copy the formula & press Ctrl + G.  Paste the formula & click ok.  You'll
 |  |  |  |  |  |  | see that it points to the range.
 |  |  |  |  |  |  | But we manually added in the height & width in the above eg & this can be replaced by using the COUNTA formula.
 |  |  |  |  |  |  | COUNTA Formula
 |  |  |  |  |  |  | This formula counts the non-blank cells there is in a range stated.
 |  |  |  |  |  |  | COUNTA(value1, [value2],…)
 |  |  |  |  |  |  | We only have to state the range for value 1.  [NAME] has a straight bracket, which means it's optional.  In this case, we
 |  |  |  |  |  |  | will use column A.
 |  |  |  |  |  |  | 15 |  | COUNTA($B:$B)
 |  |  |  |  |  |  | We have 2 rows of main headers + 1 Revenue header & 12 types of revenue.  But there is a standard 2 empty spaces in
 |  |  |  |  |  |  | between the main header & the Revenue header Excel will miss out.  Since it's always going to be there, we can add it manually.
 |  |  |  |  |  |  | 17 |  | COUNTA($B:$B)+2
 |  |  |  |  |  |  | Hence, our final formula would be as such:
 |  |  |  |  |  |  | #N/A |  | OFFSET($B$2,0,0,COUNTA($B:$B)+2,5)
 |  |  |  |  |  |  | The width can still remain as a fixed number, unless you are expecting different no of columns every month.  Then you can use
 |  |  |  |  |  |  | CountA for that too.  Now you may try adding A13 below & paste the formula in Ctrl + G.  It will take A13 into account.  Take
 |  |  |  |  |  |  | note to lock the cell formula by indicating the dollar signs as shown above.
 |  |  |  |  |  |  | Unfortunately… there is a final step to do!  To place this formula in the Named Range.
 |  |  |  |  |  |  | When we set a Print Area, Excel automatically adds it in the Named Range. To go there, click [NAME] or [NAME] -> Name
 |  |  |  |  |  |  | Manager in Excel 2007
 |  |  |  |  |  |  | You can see that Excel already has a Named [NAME] called "Print Area".  All you need is to paste the formula at the bottom.
 |  |  |  |  |  |  | And you'll never have to adjust your Print Area ever~ =)
 |  |  |  |  |  |  | This method is also applicable for Pivot Tables where the data range is always changing.  There are 2 additional steps
 |  |  |  |  |  |  | though.  Excel will not give you a Named Range like Print Area.  Simply click "New" in the same screen above.  Name the
 |  |  |  |  |  |  | Range & insert the formula.  2nd, go to the Data Range in the Pivot Table & insert the Name you just added.
 |  |  |  |  |  |  | If you forgot what is the name you gave for the Named Range, press F3 to select them.
 |  |  |  |  |  |  | It is slightly different when you are doing this in Charts.  Right click on any part of the Chart & choose "Select Data".
 |  |  |  |  |  |  | You can then choose to edit the Legend series or the Axis Labels.
 |  |  |  |  |  |  | By clicking any of them, be sure to retain the worksheet name & just replace the ones behind with the Named Range.  (i.e.
 |  |  |  |  |  |  | Keep Sheet1! & replace $[NAME]).  The worksheet name always ends with "!".  So replace anything after that.