Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
File, print area, clear area, is not working | New Users to Excel | |||
print area across the freeze panes area | Excel Worksheet Functions | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) |