Print Area
Posted by Loon on January 28, 2002 10:25 AM
I have formulas on the first line whic I need for as many lines as there are data but I dont know how many lines of data as it will often vary
Sometimes one page sometimes two some times more etc
So as the fofula workes for as many pages as needed I dragged the formula down to 1000 rows
now when I go to print preview there are many pages
how do I get round this?
Obviously I want my formulas to work if there are loads of records but I only want to print out the apropriate pages?
I know that I could drag as I go or set print area manually but I may not be thwe only one usint the spread sheet and others have a phobia when it comes to anythiing other than type and print
I'm not the best at macro's but I guess I need to do something here
:)
Thnaks
Posted by Mark W. on January 28, 2002 10:41 AM
Create a dynamic range... Suppose, Sheet1!A1:A5
contains {"Field1";1;45;6;7} and their correspond-
ing column B values are calculated using =Field1^2.
Let's also say that you've allowed for values
up to and including row 10. Therefore, Sheet1!B6:B10
contain zeroes that your don't want printed.
This can be done using the Sheet tab of the
Page Setup dialog. Now, let's define your
Print_Area using the Insert | Name | Define...
menu command. First, enter "Sheet1!Print_Area"
into the "Names in workbook" field on the Define
Name dialog. In "Refers to" field enter the
formula...
=Sheet1!$A$2:OFFSET(Sheet1!$A$2,MATCH(9.99999999999999E+307,Sheet1!$A:$A)-2,1)
After pressing [ Add ] and [ OK ] your print
range will adjust based on the enteries in column
A.
First set row 1:1 as your Print_Titles.
Posted by Loon on January 28, 2002 2:06 PM
I'm sorry I don't understand
the last line of input would be when I type Total
the totals depend on "Total" being typed in field I
if feild I = "Total" then J K an L wil be totaled
so would it be posible to set the print area depending on the word "Total"
being typed in field I
Thanks
Posted by Mark W. on January 28, 2002 2:32 PM
Yes, just change the Print_Area definition to...
=Sheet1!$I$2:OFFSET(Sheet1!$I$2,MATCH("Total",Sheet1!$I:$I)-2,3)
This formula will include columns I:L in the Print_Area
beginning a row 2.
Posted by Mark W. on January 29, 2002 6:31 AM
Correctiond!
I posted in a rush at the end of the business day
and should have employed an exact match as shown
below...
=Sheet1!$I$2:OFFSET(Sheet1!$I$2,MATCH("Total",Sheet1!$I:$I,0)-2,3)
