dynamic print area

[GeorgeW]

I have a workbook whose rows and columns contents may vary (although the
formulas in the cells will remain because they are external links to a data
file). There are 6 sheets which all will need to use the solution
independently.

What I need to do is have my Print Area be dynamic based on the cell
contents (only print a block of cells that have non 0's and blanks).

I have a cell whose formula yields a text string which I want the Print Area
command to use. When I try to define a Name and point to the cell containing
the text string, it uses the cell location not the contents. How can I change
that?

The following formula gets my height based on amount of text in column A and
width based on Row 8.
=CONCATENATE("$A$1:"&"$"&CHAR(65+MAX(A8:V8)-1)&"$"&COUNTA(A1:A65535))
I realize this will only work for the first 24 columns but I can fix it
beyond that if I get to that point.

As stated above, I will need to apply this method to each sheet within the
workbook as they each may be different. (So I'll need different Dynamic Areas
per sheet.)

I have tried to use the OFFSET command but with no luck.

All ideas will be tried and appreciated. Thanks in advance.
George

[neillcato]

George,

You can define the range with offsets like this:

=OFFSET($A$1,0,0):OFFSET($A$1,COUNTA($A:$A)-1,21)

The column range is NOT dynamic in this formula, so replace 21 in the formula with the total number of columns you have minus 1.

Example: for 30 columns enter 29

Go Insert/Name/Define

Define this name: print_area
in the "refers to:" area paste the formula from above

Do this for each sheet.


Neill

[GeorgeW]

Neillcato, The columns must be dynamic as the data file can have between 7
and 70 columns of data (thus the reason to exclude what could be a great deal
of
white space). I will try the OFFSET method again but don't hold much optimism.
Any other suggestions?


"neillcato" wrote:

>
> George,
>
> You can define the range with offsets like this:
>
> =OFFSET($A$1,0,0):OFFSET($A$1,COUNTA($A:$A)-1,21)
>
> The column range is NOT dynamic in this formula, so replace 21 in the
> formula with the total number of columns you have minus 1.
>
> Example: for 30 columns enter 29
>
> Go Insert/Name/Define
>
> Define this name: print_area
> in the "refers to:" area paste the formula from above
>
> Do this for each sheet.
>
>
> Neill
>
>
> --
> neillcato
> ------------------------------------------------------------------------
> neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
> View this thread: http://www.excelforum.com/showthread...hreadid=514952
>
>

[neillcato]

George,

Define the name "print_area" and paste this formula where it says "refers to". Repeat this for each of the six sheets - the column range is dynamic based on row 8 as in your previous example:

=OFFSET($A$1,0,0):OFFSET($A$1,COUNTA($A:$A)-1,COUNTA($8:$8)-1)


Neill

PS - note that the value 0 is counted by the function COUNTA

[GeorgeW]

This works for 1 sheet but always adds the sheet name in quotes to the
formula. Then it can't work on the other sheets because they aren't the same
size as the first sheet where the formula was defined in.
To simplify/clarify: Sheet1 is 5x5; Sheet2 is 7x7; Sheet3 is 10x10.
If I define the Print_Area in Sheet1 then all sheets are sized to 5x5. I
would like to have each sheet have their own size definition.

Thanks For getting me this far. I'm excited that it works this well.

PS One other minor bug found in testing: the show page breaks follows the
print area when reducing the number of columns (by taking out a value in row
8). When I put a value back in it doesn't get larger but does change if I do
a print preview. F9 doesn't make it recalculate this formula. So long as it
works this is a minor detail.

"neillcato" wrote:

>
> George,
>
> Define the name "print_area" and paste this formula where it says
> "refers to". Repeat this for each of the six sheets - the column range
> is dynamic based on row 8 as in your previous example:
>
> =OFFSET($A$1,0,0):OFFSET($A$1,COUNTA($A:$A)-1,COUNTA($8:$8)-1)
>
>
> Neill
>
> PS - note that the value 0 is counted by the function COUNTA
>
>
> --
> neillcato
> ------------------------------------------------------------------------
> neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
> View this thread: http://www.excelforum.com/showthread...hreadid=514952
>
>

[GeorgeW]

I have the OFFSET command working now in multiple sheets. [I had a bit of a
problem getting the 'sheet name!' to be correct on each sheet but have that
now.] Remaining problem is that the OFFSET command sometimes getting replaced
by the absolute cell references (the results) when the workbook is closed. It
doesn't do it all the time and I haven't quite figured out what makes it
occur. If it won't stay then all this work is for nothing.
Any thoughts on where to look???

"GeorgeW" wrote:

> This works for 1 sheet but always adds the sheet name in quotes to the
> formula. Then it can't work on the other sheets because they aren't the same
> size as the first sheet where the formula was defined in.
> To simplify/clarify: Sheet1 is 5x5; Sheet2 is 7x7; Sheet3 is 10x10.
> If I define the Print_Area in Sheet1 then all sheets are sized to 5x5. I
> would like to have each sheet have their own size definition.
>
> Thanks For getting me this far. I'm excited that it works this well.
>
> PS One other minor bug found in testing: the show page breaks follows the
> print area when reducing the number of columns (by taking out a value in row
> 8). When I put a value back in it doesn't get larger but does change if I do
> a print preview. F9 doesn't make it recalculate this formula. So long as it
> works this is a minor detail.
>
> "neillcato" wrote:
>
> >
> > George,
> >
> > Define the name "print_area" and paste this formula where it says
> > "refers to". Repeat this for each of the six sheets - the column range
> > is dynamic based on row 8 as in your previous example:
> >
> > =OFFSET($A$1,0,0):OFFSET($A$1,COUNTA($A:$A)-1,COUNTA($8:$8)-1)
> >
> >
> > Neill
> >
> > PS - note that the value 0 is counted by the function COUNTA
> >
> >
> > --
> > neillcato
> > ------------------------------------------------------------------------
> > neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
> > View this thread: http://www.excelforum.com/showthread...hreadid=514952
> >
> >

[neillcato]

GeorgeW,

In testing this works well for me as long as all of the cell references are absolute. Any manual methods of setting print area will overwrite the formula. I would stay away from the page break preview and use print preview instead.

When you first open the workbook the print area selection is always missing. Run a print preview and you will see it will calculate correctly. You are right about the sheet references needed in the formula: they have to match the active sheet when you define the name.

Ex: =OFFSET(Sheet3!$A$1,0,0):OFFSET(Sheet3!$A$1,COUNTA(Sheet3!$A:$A)-1,COUNTA(Sheet3!$8:$8)-1)

Where "Sheet3" is the name of the active worksheet.

I hope this helps.

Neill

[GeorgeW]

Neill, By George (excuse the pun) I think we have it. Thank You very much for
your patience and help. You can't know how much it is appreciated.

I kind-of came to the same conclusion this morning about the manual print
range adjustments. That is probably it. I have opened/closed the workbook
numerous times (without making adjustments) and the formula has stayed. I now
have the sheets and workbook password rotected so the users can't mess it up
(although it does change if they manually change the print area). Perhaps
more security settings need to be checked/cleared, but that's another topic
if needed.

As a side note, I have the OFFSET formula type into a cell for cut/paste. In
that formula, I didn't type in the sheet reference so I can copy it and then
paste it into each sheet.

Thanks again for your help.
George

"neillcato" wrote:

>
> GeorgeW,
>
> In testing this works well for me as long as all of the cell references
> are absolute. Any manual methods of setting print area will overwrite
> the formula. I would stay away from the page break preview and use
> print preview instead.
>
> When you first open the workbook the print area selection is always
> missing. Run a print preview and you will see it will calculate
> correctly. You are right about the sheet references needed in the
> formula: they have to match the active sheet when you define the name.
>
> Ex:
> =OFFSET(Sheet3!$A$1,0,0):OFFSET(Sheet3!$A$1,COUNTA(Sheet3!$A:$A)-1,COUNTA(Sheet3!$8:$8)-1)
>
> Where "Sheet3" is the name of the active worksheet.
>
> I hope this helps.
>
> Neill
>
>
> --
> neillcato
> ------------------------------------------------------------------------
> neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
> View this thread: http://www.excelforum.com/showthread...hreadid=514952
>
>