Dynamic Print Range

[Mgall]

I am trying to set the print area to print only the area of the worksheet that contains text. Some cells contain formulas, so if you print the worksheet, 3 pages will be printed while only one may actually contain information.

I created a named range 'MyPrintArea' (with the scope set to the sheet level) and entered the following formula:

=OFFSET(Sheet1!$A$1,0,0,22+COUNTIF(Sheet1!$A$23:$A$112,">0"),12)

This part works fine, and will return the correct range everytime as well as update as the sheet changes. The problem arises when i go to set the 'Print_Area' to equal my dynamic range 'MyPrintArea'. Instead of the named range, excel automatically updates the "refers to" area to the static range that 'MyPrintArea' is equal to at that time. Ex:

=MyPrintArea
changes to
=Sheet1!$A$1:$L$24

I have tried entering the offest formula directly into 'Print_Area' and get the same result. I have seen examples where this works fine for other users, and 'Print_Area' remains equal to the named range and updates along with it.

Any thoughts? <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[Steve=True]

Are you doing it in the Name Manager?

I created any random print area, then i went into the name manager and changed the value of Print Area = MyPrintArea.

 

[Mgall]

Thanks for the response.

Yes, I am using the name manager. I have also tried using the page setup options, but I still get the same results- where the dynamic range or formula is converted into a static range.

I just tried doing this in a blank workbook and it works, so I think my problem has something to do with the formatting of my worksheet... I have some cells merged and others conditionally formatted. Could there be an issue there?

 

[Mgall]

I think my issue was with the page formatting. When I switched the view from page layout to normal, the fomula started working... but now if I put it back into the page layout the formula won't work again. I guess I can live with the normal view, but if anyone has a workaround or can tell me why this happens I'd appreciate it.
<!-- / message -->

<!-- controls -->