1

I have a spreadsheet that I created in Excel 2011 for the Mac. In it, I am able to create a dynamic print range and it works well.

However when I open the spreadsheet in Excel 2010 for Windows, I look at the named ranges and my dynamic print range has becomes static.

The worksheets that I am printing contain formulae in cells down to row 101. I calculate the last row that has printable data and place that in a named range called Last_Row. I then refer to Last_Row when I calculate the print area in the named range "Print_Area".

As I said, on my Mac when the number of rows with printable data changes, so does the number of pages printed. In Excel 2010, however, the range "Print_Area" becomes static and doesn't update as the number of printable rows changes.

Has anyone had this issue before, or know the solution?

Update

I also have the same behaviour in Excel 2013.

The formula that I am using is:

=OFFSET('Run1'!$A$1,0,0,'Run1'!Last_Row,9)

The value "Last_Row" is also a named range and it works without any problems on any version of excel.

Improve this question

4 Answers 4

Reset to default
0

I have the same issue in Win Excel 2010 but only sometimes. Some workbooks retain the dynamic formula, others convert to static. I just discovered this yesterday so don't have more data yet on full behavior.

OK, here's an answer I've uncovered that does not solve the problem but does avoid it:

Don't use the Print Preview option anywhere.

This option consistently converts a worksheet's Print_Area expressed as a formula to its absolute range. I tested this repeatedly using Adobe vs Print Preview; Adobe does not result in conversion but Print Preview does. I'm assuming hard-copy printing (for those who use paper) works the same as Adobe.

Improve this answer
3
  • This is not an answer to the question asked. This is merely a comment. Please us the comment function in the future.
    – Uwe Plonus
    Jun 19, 2013 at 14:42
  • Apologies. I missed the Comment option. I've edited my "answer" with a real answer, I believe.
    – tesuji
    Jun 19, 2013 at 15:43
  • Welcome to Super User! This is a Q&A site, so posts that don't actually answer the question get removed. Once you have participated for a bit and earned enough reputation you will be able to post comments (like this one) if you have other information to add, e.g. workarounds or related questions,. Find out more about Super User on the about page and in our help center.
    – Daniel Beck ♦
    Jun 19, 2013 at 19:05
0

I've also seen this behaviour! The solution for me was to make sure I set the dynamic print range in 'Normal View' rather than 'Page Layout' view. In 'Page Layout' view I could define the dynamic print range in the name manager with the offset function, however as soon as I close and re-opened the name manager, excel had converted it to a static range (no more offset formula). As I say, doing it all in Normal mode (View > Normal) solved it for me.

Improve this answer
0

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

Dynamic Print Range

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. Page Layout 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 print area. Page Setup

If you press the OK button on any tab, it takes the hardcoded print area value and updates the Named Range with that. Named Range After Change Press cancel and your Named Range stays dynamic. Make sure your sheet is setup how you want it, then insert your dynamic 'Print_Area' Named Range.

Improve this answer
0

The only way I've managed to make a dynamic print area "stick" is to use Name Manager to edit the print area and then do nothing Print Preview, Page Setup or even just revisiting Name Manager will reset it to a fixed range

Not satisfactory

jim

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .