Dynamic Name as Excel Print Range

[SanAntonioSpurFan]

I've created a Dynamic Name in Excel that determines the correct print range. When I type the dynamic name "MyPrintRange" into the Print Area on Excel's Page Setup it know the correct range but then hard codes the cell reference as A1:G50 and never refers back to the dynamic name.

How do you "hard code" the dynamic name into the page setup so it will always print the dynamic range.

Is there away to do this without using a seperate print button? Seams so simple, yet I can't figure it out! Thanks for any help...

 

[PHV]

Have you tried to make the PrintArea name dynamic ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Hi,

Name your dynamic range "Print_Area"



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

Skip,

I named the range "Print_Area" and it still only works the first time you click print preview. After you click close from print preview and go into the page setup and look at the print area it shows a "hard coded" range like A1:Q5 or whatever. I noticed that it also seems to overright my named range "Print_Area" with this "hard coded" range. When I click delete on the named range it then returns the "Print_Area" named range back to the formula I want to use.

My delima is that my "Print_Area" will always and I need it to always reference the named range: "Print_Area".

Do you have something else up your sleeve?

 

[SkipVought]

SA,

I Define Print_Area as Sheet1!A1:B5

I Print Preview -- A1:B5

I Define Print_Area as Sheet1!A11:B55

I Print Preview -- A11:B55

Is this NOT what is happening along the riverwalk?


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

Skip,

I love your sence of humor!

I define Print_Area as:
=OFFSET('Brand OpGap'!$A$1,0,0,COUNTIF('Brand OpGap'!$K$1:$K$207,">0&quot,13)

I Print Preview and get the correct range, then excel seems to hard code the values in the print range like A1:Q30 or something.

I want the print_area to always get it's range from "Print_Area". Print area will change on this sheet when new data comes in. The formula works fine, however, the print_area is creating dirty water on the river walk.

HELP!

 

[SkipVought]

=OFFSET('Brand OpGap'!$A$1,0,0,COUNTA('Brand OpGap'!$K$1:$K$207),13)

if you use COUNTIF, you miss counting values of zero. this shrinks the number of rows referenced from A1.

This argument needs to be the number of row that need to be printed.

My OFFSET adjusts Print_Area as I enter data -- I can see the dancing ants rectangle E X P A N D I N G! ;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

That's the whole point of using COUNTIF - I don't want to count zero's. I have conditional formatting applied from range A5:K207 that says if K7=0 make the font white so the user does not see it. If a new value comes aboard, and it will always be greater than zero, the cell turns black and a new number appears. The COUNTIF captures this new value in the range. The COUNTA graps the whole range A1:K207. Any clue?

 

[SkipVought]

Are all the zeros together and below the non-zero data?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

YES! and will always be!

 

[SkipVought]

this one works, too
[tt]
=OFFSET('Brand OpGap'!$A$1,0,0,COUNTIF('Brand OpGap'!$K$:$K$,">0&quot,13)
[/tt]
Use

$K$:$K$

but NOT

$K$K$K ;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

Skip,

It does not work for me. It give me a error. If I put $K$1:$K$207 in, it works. I am not having a problem with the named range working. It works perfect. The formula is good. My problem is that Excel's Print_Area does not seem to maintain a constant reference to the named range. It works the first time, but then hard codes the last named range as a range like $A$1:$K$40. Does that help? It you would like me to email the file, it's small, I can...let me know, and thanks for all your time!

 

[SkipVought]

skip.metzger @ lmco.com

and, of course, remove the spaces

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SanAntonioSpurFan]

Skip,

Did you receive the file? We have had reports that our email server is acting up. I sent a couple of test messages to you and have not heard a reply. Just want to make sure you got it. Let me know...Thanks..

 

[SkipVought]

I am not at that eMail address today. I had not received it yesterday.

skip @ theofficeexperts.com



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884