Dynamic print range changes to fixed range

jmplajr

New Member
Joined
Mar 22, 2016
Messages
1
I have Excel 2016.

Every time I set a dynamic print range it quickly reverts back to a fixed range.

Here are my ranges:
Print_Area Scope: PURCHASE ORDERS Refers to: ='PURCHASE ORDERS'!purchase_orders_print_area
purchase_orders_print_area Scope: PURCHASE ORDERS Refers to: =OFFSET('PURCHASE ORDERS'!$A$1,0,0,'PURCHASE ORDERS'!$L$1,11)

Cell L1 has this formula: =COUNTA($A:$A)

The named range purchase_orders_print_area has no problem keeping its formula, but as soon as i save and reopen the file the print_area named range changes to something like ='PURCHASE ORDERS'!$A$1:$K$116

I don't know if it makes a difference, but I also have the sheet protected, and shared workbook turned on.

I have this same problem across several excel files, I have checked everything I can find on the web, and I don't know how to fix it.

Please help.

Thanks.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

j3rdy

New Member
Joined
Dec 30, 2013
Messages
6
I have Excel 2016.

Every time I set a dynamic print range it quickly reverts back to a fixed range.

Here are my ranges:
Print_Area Scope: PURCHASE ORDERS Refers to: ='PURCHASE ORDERS'!purchase_orders_print_area
purchase_orders_print_area Scope: PURCHASE ORDERS Refers to: =OFFSET('PURCHASE ORDERS'!$A$1,0,0,'PURCHASE ORDERS'!$L$1,11)

Cell L1 has this formula: =COUNTA($A:$A)

The named range purchase_orders_print_area has no problem keeping its formula, but as soon as i save and reopen the file the print_area named range changes to something like ='PURCHASE ORDERS'!$A$1:$K$116

I don't know if it makes a difference, but I also have the sheet protected, and shared workbook turned on.

I have this same problem across several excel files, I have checked everything I can find on the web, and I don't know how to fix it.

Please help.

Thanks.

I've seen this on quite a few sites with no definitive answer, so have decided to post here, what I have seen and what I have done to avoid it.

This happens when you change anything in the Page Setup screen dialog. This is reached from the print preview screen 'Page Setup' hyperlink or via the Page Layout Tab and pressing Print Titles.
It has four tabs: (Page, Margin, Header/Footer and Sheet) If you press the 'Sheet' tab on the page setup dialog, it has the formulated hardcoded values of the current dynamic print area.
If you press the OK button on any of the 4 tabs, it takes the hardcoded print area value and updates the Named Range with that. Press cancel and your Named Range stays dynamic. Make sure your sheet is setup how you want it to print first, then insert your dynamic 'Print_Area' Named Range and save. You shouldn't have any issues after that unless you make changes to page setup.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top