Ignore Blank Cells

[SEF]

I update a workbook weekly in separate worksheets. My master worksheet links
to the appropriate cells for the updated data. These are simple percentage
numbers (not forumlas, etc.) and only need to be a one-on-one link. The
problem I have is that if one of the cells is blank, it returns a 0 and I
need to to stay blank. Any ideas? There are about 57,000 cells I am working
with so I really don't want to have to update manually all blanks.

 

[Wouter HM]

Hi SEF,

As long as you want to hide all zero values on a sheet you can use in
excel 2003
From the Tools menu select Options
On tab View uncheck Zero Values

If you only would like to hide the zeros from the formula results you
can use conditional format for the result cells.
How this works depends on the excel version you arre using.
Look at the online help.

An other option is to use the number format for those cell with
something like:
#.##0_);[Red](#.##0);[White]0;[Blue]@

HTH,

Wouter

 

[Gord Dibben]

If linking formulas are like =Sheet1!A1 change them to

=IF(Sheet1!A1="","",Sheet1!A1)

Copy down and across.

Or use a macro to change all existing formulas.

Sub ZeroTrapAdd()
Dim mystr As String
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(*" Then
mystr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[fisch4bill]

Probably the simplest way is to go into the display options for Excel 2007
and uncheck the box [Show a zero in cells that have zero value]; it's in the
Excel Options that you access at the bottom of the Office Button menu, In
Excel 2003, it's found in the Tools>Options>View menu (it's a check box that
says Zero values). If these are NOT checked you shouldn't see the zeroes in
the cells linked to empty cells. You may have to re-open the file for it to
take effect.

HTH
Bill

 

[fisch4bill]

I should probably have mentioned that in 2007, it's under Excel
Options>Advanced>Display options for this worksheet.

Bill

 

[SEF]

Thank you all - the macro was especially helpful!

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thank you all - the macro was especially helpful!