Formula to govern Print Area

[kwrohde]

I want to set my print area based on a formula. For example

if the value in cell I1 = 1

then print range A1:G23

otherwise print range H24:M44



I have found that you can type a statement like this:

=if(I1=1,A1:G23,H24:M44)

into the print area box on the page setup tab and if I1=1 then excel
will replace =if(I1=1,A1:G23,H24:M44) with A1:G23

therefore printing what I wanted but lossing the formula.

Any ideas?

 

[Dave Peterson]

Try this.

Insert|Name|Define
In the names in workbook box:
'sheet1'!Print_Area

in the refers to box:
=INDIRECT(IF('Sheet1'!$I$1=1,"$A$1:$G$23","$H$24:$M$44"))

Change the worksheet name accordingly.

 

[kwrohde]

Im not sure what you mean when you say "in the refers to box:". If you
mean the print area box on the sheet tab of page setup from the file
menu then your formula:

"=INDIRECT(IF('Sheet1'!$I$1=1,"$A$1:$G$23","$H$24:$ M$44"))"

works similar to my formula:

"=if(I1=1,A1:G23,H24:M44)".

Lets say that cell A1 = 1.
If you enter my formula or your formula into the Print area box, click
ok, close the page setup window, and reopen the page setup window the
Print are box will read A1:G23. Which is the area I wanted to print.
The problem arrises when say cell A1 changes from 1 to some other
value. The print area will not change because the formula is no longer
there. any ideas.