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

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

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.

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.

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
.

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
.