Sheet1!Print_Area =offset($A$1,0,MonthNo+2,counta($A:$A),2)
If MonthNo is 1 Columns C and D will print for as many rows as there are entries in column A.
If MonthNo is 2 Columns D and E will print for as many rows as there are entries in column A.
And so on...
Using calculations for Print_Area provides the basis for some interesting possibilities:
If I identify a need to print everything on one page in certain circumstances, but on two pages (that need to be carefully controlled) in other circumstances, I can set up a number of calculated names and "cascade" these to create a sophisticated multi-range print range.
e.g.
Sheet1!OnePage =offset($A$1,0,3,counta($A:$A),MonthNo)
Sheet1!Pg1 =offset($A$1,0,3,counta($A:$A),6)
Sheet1!Pg2 =offset($A$1,0,6+3,counta($A:$A)+1,MonthNo)
Sheet1!TwoPages =Sheet1!Pg1,Sheet1!Pg2
Sheet1!Print_Area =if(MonthNo>6,Sheet1!TwoPages,Sheet1!OnePage)
A word of warning: if the areas that make up TwoPages fit within a simple rectangular area, with no cells within that rectangle unselected, Excel will replace the calculation for Print_Area with the absolute cell references of the upper left and lower right cells when MonthNo > 6. I've even seen Excel crash in these circumstances. However, to avoid this possibility, I find it convenient to include an extra row in one of the ranges. (That is why the calculated number of rows for Pg2 is counta($A:$A)+1, one more than the counta($A:$A) for Pg1)
