Dynamic Print Range Resets Itself

[miketastic]

Hello,

I have set up a dynamic print range via the Name Manager with the following formula:

=OFFSET('WIRES'!$A$1,0,0,COUNT('WIRES'!$E:$E)+5,6)

The formula works fine, but when I use it (both directly as the Print_Area and as a separate Name for Print_Area to refer to) the formula disappears and only the calculation is left. I can't get the formula to stick so that it actually is dynamic.

Any help is CRAZY appreciated!

Thanks

 

[JoeMo]

Hello,

I have set up a dynamic print range via the Name Manager with the following formula:

=OFFSET('WIRES'!$A$1,0,0,COUNT('WIRES'!$E:$E)+5,6)

The formula works fine, but when I use it (both directly as the Print_Area and as a separate Name for Print_Area to refer to) the formula disappears and only the calculation is left. I can't get the formula to stick so that it actually is dynamic.

Any help is CRAZY appreciated!

Thanks

The formula disappears from where?

 

[miketastic]

From the "Refers to:" part of Print_Area. Whether I put the formula directly there or put the formula under another name to have Print_Area refer to that Name instead, Print_Area always reverts to the calculation of the formula.

So if my formula is: =OFFSET('WIRES'!$A$1,0,0,COUNT('WIRES'!$E:$E)+5,6)

Print_Area turns into ='WIRES'!$A$1:$F$20

 

[JoeMo]

From the "Refers to:" part of Print_Area. Whether I put the formula directly there or put the formula under another name to have Print_Area refer to that Name instead, Print_Area always reverts to the calculation of the formula.

So if my formula is: =OFFSET('WIRES'!$A$1,0,0,COUNT('WIRES'!$E:$E)+5,6)

Print_Area turns into ='WIRES'!$A$1:$F$20

Not able to reproduce the problem using Excel 2010 with Print_Area defined at workbook level and set equal to:
OFFSET('WIRES'!$A$1,0,0,COUNT('WIRES'!$E:$E)+5,6)
the print area adjusts dynamically on the worksheet with tab name WIRES.

 

[miketastic]

I'm using 2007. Do you think that could be making a difference? Though not being dynamic would be SUPER pointless.... excellll!!!!

 

[JoeMo]

I'm using 2007. Do you think that could be making a difference? Though not being dynamic would be SUPER pointless.... excellll!!!!

Don't see any reason why 2007 would be different. Close the workbook and Excel. Open a new instance of Excel and a new workbook and name a sheet WIRES and try again.

 

[miketastic]

I got it to work on a test workbook! Then I noticed that the scope of the Name was for the whole workbook. So I went to my original workbook and broadened the scope which totally fixed my problem... initially. I have two tabs that need to reference this Name for the dynamic printing purposes, but it only works on one tab. When I try selecting it for the second tab, it does the same thing.

Any ideas?

 

[JoeMo]

I got it to work on a test workbook! Then I noticed that the scope of the Name was for the whole workbook. So I went to my original workbook and broadened the scope which totally fixed my problem... initially. I have two tabs that need to reference this Name for the dynamic printing purposes, but it only works on one tab. When I try selecting it for the second tab, it does the same thing.

Any ideas?

If you want to use the dynamic range on two sheets, say WIRES and PLUGS, then define a name at Sheet level for each sheet. The name can be Print_Area for both sheets (no conflict b/c its sheet-level name).
For WIRES:
=OFFSET(Wires!$A$1,0,0,COUNT(Wires!$E:$E)+5,6)

For PLUGS:
=OFFSET(Plugs!$A$1,0,0,COUNT(Plugs!$E:$E)+5,6)

 

[miketastic]

Not to go back and forth into forever on this, but I tried exactly that and now the formulas are disappearing for both of them! I've closed and opened the worksheet, but I just can't seem to get the formula to stick.

:/

 

[JoeMo]

Not to go back and forth into forever on this, but I tried exactly that and now the formulas are disappearing for both of them! I've closed and opened the worksheet, but I just can't seem to get the formula to stick.

:/

I can't reproduce your problem. I can define print areas on two different sheets using your offset formula with references to ranges on each specific sheet. If I activate one of those sheets, press F5 and type Print_Area, and then press Enter the print area is selected in accord with the formula's dependence on the count of numeric entries in col E.