Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Can't We Set Print Area with condition?

Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Can't We Set Print Area with condition?

Need Help on axis problem in a chart wrote:
Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00



Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RAZ RAZ is offline
external usenet poster
 
Posts: 120
Default Can't We Set Print Area with condition?

thanks, but I could not find where to add this
please explain where to type this equation or function you wrote. (I am
using excel 2007)
thanks.

"Glenn" wrote:
Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Can't We Set Print Area with condition?

I am using Excel 2003, but I think it should still be the same...

Insert / Name / Define

Names in workbook: Print_Area
Refers to: =INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


Raz wrote:
thanks, but I could not find where to add this
please explain where to type this equation or function you wrote. (I am
using excel 2007)
thanks.

"Glenn" wrote:
Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RAZ RAZ is offline
external usenet poster
 
Posts: 120
Default Can't We Set Print Area with condition?

found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book



"Glenn" wrote:

Need Help on axis problem in a chart wrote:
Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00



Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Can't We Set Print Area with condition?

Did you replace "Sheet1" with the name of your sheet?

Raz wrote:
found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RAZ RAZ is offline
external usenet poster
 
Posts: 120
Default Can't We Set Print Area with condition?

Yes I did

"Glenn" wrote:

Did you replace "Sheet1" with the name of your sheet?

Raz wrote:
found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Can't We Set Print Area with condition?

It sounds like you mistyped: Print_Area

I would use this version of Glenn's instructions:

Insert|Name|Define

Names in workbook: 'Sheet1'!Print_Area
Refers to: =OFFSET('Sheet'1!$A$1,0,0,countif('sheet1'!$a:$a," 0"),6)

If you have a header row, you'll want to adjust that countif() portion:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a ,"0"),6)

And the 6 represents the number of columns to use.

Ps. If you go into file|page setup, you may find that the print range is
changed to a specific range. And you'll have to reapply the name.

Raz wrote:

found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book

"Glenn" wrote:

Need Help on axis problem in a chart wrote:
Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00



Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RAZ RAZ is offline
external usenet poster
 
Posts: 120
Default Can't We Set Print Area with condition?

Thanks Dave,
I am using this one:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a ,"0"),6)

its working now, but i need to fix to apply to appropriate rows and columns.
could you please explain a little more the numbers in the function. I do
have Header Row that I need to print on every page that is Rows 1 to 13

for example.
to print col A to K (this is fixed, wont change)
to check the equation 0 in Col C

in the function
Sheet1'!$A$1,0,0,1
what are these 1, 0, 0, 1 means?

Please take a min to explain these, I appritiate your help. thanks

"Dave Peterson" wrote:

It sounds like you mistyped: Print_Area

I would use this version of Glenn's instructions:

Insert|Name|Define

Names in workbook: 'Sheet1'!Print_Area
Refers to: =OFFSET('Sheet'1!$A$1,0,0,countif('sheet1'!$a:$a," 0"),6)

If you have a header row, you'll want to adjust that countif() portion:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a ,"0"),6)

And the 6 represents the number of columns to use.

Ps. If you go into file|page setup, you may find that the print range is
changed to a specific range. And you'll have to reapply the name.

Raz wrote:

found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book

"Glenn" wrote:

Need Help on axis problem in a chart wrote:
Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00


Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


--

Dave Peterson

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RAZ RAZ is offline
external usenet poster
 
Posts: 120
Default Can't We Set Print Area with condition?

thanks so much for your help, Its working finally, except one problem,
Its not printing the last page, with about 10 rows at the end.

the name I am using is
'Sheet1'!Print_Area
the function I am using is
=OFFSET(Sheet1!$A$1,0,0,1+COUNTIF(Sheet1!$C:$C,"0 "),11)

I want it to print till it finds a zero in the column C


Please Help


"Glenn" wrote:

Did you replace "Sheet1" with the name of your sheet?

Raz wrote:
found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Can't We Set Print Area with condition?

The =offset() function works like this:

=offset(reference,rows,cols,height,width)

The range is based on A1.

The topleft corner is offset 0 rows and 0 columns from that cell--it stays A1.

The number of rows (height) is 1+countif() rows.
The width is 6 (A:F)

So it sounds like you could add 13 to the number of rows to offset, point at
column C in the countif() portion and make that argument 11 (column K).

=OFFSET('Sheet1'!$A$1,0,0,13+countif('sheet1'!$c:$ c,"0"),11)

When you're testing this, you can use:
ctrl-g (or F5 or edit|goto)
and type:
print_area

To see what's selected.

By the way, you can look at excel's help for =offset() to see more info.

Raz wrote:

Thanks Dave,
I am using this one:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a ,"0"),6)

its working now, but i need to fix to apply to appropriate rows and columns.
could you please explain a little more the numbers in the function. I do
have Header Row that I need to print on every page that is Rows 1 to 13

for example.
to print col A to K (this is fixed, wont change)
to check the equation 0 in Col C

in the function
Sheet1'!$A$1,0,0,1
what are these 1, 0, 0, 1 means?

Please take a min to explain these, I appritiate your help. thanks

"Dave Peterson" wrote:

It sounds like you mistyped: Print_Area

I would use this version of Glenn's instructions:

Insert|Name|Define

Names in workbook: 'Sheet1'!Print_Area
Refers to: =OFFSET('Sheet'1!$A$1,0,0,countif('sheet1'!$a:$a," 0"),6)

If you have a header row, you'll want to adjust that countif() portion:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a ,"0"),6)

And the 6 represents the number of columns to use.

Ps. If you go into file|page setup, you may find that the print range is
changed to a specific range. And you'll have to reapply the name.

Raz wrote:

found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book

"Glenn" wrote:

Need Help on axis problem in a chart wrote:
Hi,
I would like to set auto print area to an worksheet.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, then it prints the whole
table.


HERE IS HOW MY PAGE LOOKS

I have 2 sheets, on the first sheet I do the calculations.

second sheet is the one I need to print, which gets the results from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00


Try this for Print_Area definition in the Define Name dialog:

=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
set area in excel not giving me option to set print area? J Littlebear Excel Discussion (Misc queries) 4 April 23rd 23 09:04 PM
File, print area, clear area, is not working cblind New Users to Excel 2 September 12th 07 04:51 PM
print area across the freeze panes area tom Excel Worksheet Functions 2 January 6th 07 06:23 PM
Pivot Table macro to set print area and print details of drill down data Steve Haskins Excel Discussion (Misc queries) 2 December 28th 05 05:59 PM
How do you turn off a print area for a page? (no print area) Grunen Excel Discussion (Misc queries) 4 October 8th 05 07:46 PM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"