Print areas

D

DavidObeid

Is there any way of getting excel to determine print areas depending on
cell content?

I have a spreadsheet whose print area needs to change for different
data entered and I want to be able to automatically set a print area so
the user doesn't need to fiddle with the print area manually.

Thanks in advance,

Dave
 
J

Jim Rech

If you do not create a print_area then Excel will print the entire active
range. If that's not good enough (like you want to exclude some part of the
active range yet make the rest of it dynamic) you may be able to do
something with Print_area defined as a formula. The Offset and CountA
functions are frequently used for this sort of thing. We'd need the "rules"
for determining the print_area to come up with a formula for you.

As an example, say you had contiguous data in cell A1 and down and you
wanted that data in a print area as you added more. Yet you also wanted to
only go out to column H no matter how much data was beyond H. In that case
you could define the name "Print_Area" as a formula in the Define Name box
like this:

=OFFSET(Sheet1!A1,0,0,COUNTA(Sheet1!A:A),8
 
D

DavidObeid

The sheet I am working on needs to print from cell B10 down to the cell
in column . The row that I print down to I need to be determined based
upon the contents of the cells in column B.

That sounds confusing even to me and it is my spreadsheet. Let me give
examples.

The print range I want will always start at Cell B10.

When the user enters their data in the data entry area of the spread
sheet then column B will contain a list of descending numbers.

The list does not run all the way down to zero, but stops at either 20%
of the usr defined highest number of at someother mark specified by the
user.

The last entry in the list that I want printed will contain a "<=".

I want the last cell in my print range to be the cell in column C to
the right of the cell containg the "<=" in column B.

Have I confused you?
 
J

Jim Rech

I didn't follow what you said about the 20% but just focused on a cell
having "<=" as meaning the end of data. So try this..

Open the Define Name dialog by pressing Ctrl-F3. Create (or change) the
name "Print_Area" with this definition (please copy and paste this in, don't
try to re-type it):

=OFFSET($B$10,0,0,MATCH("*<=*",$B$10:$B$2000,FALSE),2)

and click OK. The B2000 is just my guess how far data can go. Make it
longer or shorter as you wish. Does this help?
 
D

DavidObeid

Thanks for your tip Jim.

I worked from your first suggestion, and after going through the help
file on "offset" I came up with this formula in the Print-Area
definition:

=OFFSET(Sheet1!$B$9,0,0,COUNT(Sheet1!$B$9:$B$95)+7,2)

It is now doing exactly what I wanted it to do.

Does MVP stand for "most valuable programmer"? If not, it should.
Thanks again.

In Jesus and Mary,

Dave
 
Top