Dynamic print ranges become static

[DarrenC]

I have set-up a dynamic range using Offset and Count functions to capture the range of data I want to print form a sheet.

I can type the range name into the print area box on the
File/Page Set Up/Sheet menu. This sets the range OK.

The problem is that after closing the dialog box, the range name I have typed in reverts to the cell reference of the range when I typed in the name (eg. A1:K13) instead of picking up the named range I typed in. This means that ven thought I have a dynamic range, I still have a static print range.

Is there any way I can use a dynamic range to set the print area, and have the print area change as the dynamic range changes ?

One other request is that should not use macros/VBA so I won't have to have the entire spreadsheet audited.

 

[Glove_Man]

Rather than set a dynamic range and then make the print area equal to that range, make the Print_Area range dynamic itself.

The named range Print_Area is the area that will be printed and can be made dynamic directly.

 

[Glove_Man]

Set any print area, then go to Insert | Name | Define and find Print_Area. Use the normal offset and count functions to make the area called Print_Area dynamic.

 

[DarrenC]

Problem Solved.

Many thanks.

 

[Sue Gould]

Thanks Glove Man, this just helped me as well.

 

[l_eonandr]

This works great if the Table is not moved, however, each month I copy the table and insert the copy in front of the old one then fill with data. The print range moves with the old table to the new columns the old table now occupies.
Is there a way of keeping the same columns in the print range, Ie old columns F-I, move table to right 5 columns, now new print range should be F-I, however excel has moved it to follow the table to K-N.
How can I set it for F-I and keep that even when columns are added in front of it? Is it possible?

Thanks Glove_Man