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

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

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.

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.

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
.

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
.