If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Printing cells that are blank, because there is a formula
I'm using Excel 03 and I have a column of cells that have a formula
=if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks |
#2
|
|||
|
|||
Printing cells that are blank, because there is a formula
Left to its own devices, excel will include those cells in the print range--they
contain something (that formula), so those cells will be included. But you can do something to tell excel what you want... Saved from a previous post (so you'll have to adjust the sheet names and column letters and even the columns to print (A:X or B:z????): If those formulas appear at the end of the data and you don't want to use autofilter... If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). 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 Print_Area name. JeffK wrote: I'm using Excel 03 and I have a column of cells that have a formula =if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks -- Dave Peterson |
#3
|
|||
|
|||
Printing cells that are blank, because there is a formula
Wouldn't this normally default to printing if there were something in that
column, or have you selected a defined print area?? I've worked with a similar problem which MAY provide a solution. Can you conditionally format the cells so that if the content is not "", it provides a border (even a white one)? Excel expands the print area to include anything with formatting OR content unless you've pre-determined the print area, so in theory it should only print anything which has (a) content and/or (b) a border. |
#4
|
|||
|
|||
Printing cells that are blank, because there is a formula
Even though the value is "" Excel still recognizes the cell to contain a
content (because there is a formula written). Therefore because the formula is copied down the entire column (all showing nothing), it prints 125 pages of blank pages. "Markytee" wrote: Wouldn't this normally default to printing if there were something in that column, or have you selected a defined print area?? I've worked with a similar problem which MAY provide a solution. Can you conditionally format the cells so that if the content is not "", it provides a border (even a white one)? Excel expands the print area to include anything with formatting OR content unless you've pre-determined the print area, so in theory it should only print anything which has (a) content and/or (b) a border. |
#5
|
|||
|
|||
Printing cells that are blank, because there is a formula
That works great the first time, but as you eluded to, each time the sheet
changes, the Print_Area has to be reapplied. "Dave Peterson" wrote: Left to its own devices, excel will include those cells in the print range--they contain something (that formula), so those cells will be included. But you can do something to tell excel what you want... Saved from a previous post (so you'll have to adjust the sheet names and column letters and even the columns to print (A:X or B:z????): If those formulas appear at the end of the data and you don't want to use autofilter... If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). 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 Print_Area name. JeffK wrote: I'm using Excel 03 and I have a column of cells that have a formula =if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks -- Dave Peterson . |
#6
|
|||
|
|||
Printing cells that are blank, because there is a formula
I was wrong, it works fine every time.
Thanks again Dave, saved my bacon a few times now. "JeffK" wrote: That works great the first time, but as you eluded to, each time the sheet changes, the Print_Area has to be reapplied. "Dave Peterson" wrote: Left to its own devices, excel will include those cells in the print range--they contain something (that formula), so those cells will be included. But you can do something to tell excel what you want... Saved from a previous post (so you'll have to adjust the sheet names and column letters and even the columns to print (A:X or B:z????): If those formulas appear at the end of the data and you don't want to use autofilter... If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). 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 Print_Area name. JeffK wrote: I'm using Excel 03 and I have a column of cells that have a formula =if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks -- Dave Peterson . |
Thread Tools | |
Display Modes | |
|
|