Prev: VBA code to protect cells Outside a given range
Next: Turn a range of cells into 'read only'
From: SEF on 27 Apr 2010 13:53
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.
From: Wouter HM on 27 Apr 2010 14:18
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

From: Gord Dibben on 27 Apr 2010 14:24
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

On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF(a)discussions.microsoft.com>
wrote:

>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.

From: fisch4bill on 27 Apr 2010 14:40
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

"SEF" wrote:

> 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.
From: fisch4bill on 27 Apr 2010 14:42
I should probably have mentioned that in 2007, it's under Excel
Options>Advanced>Display options for this worksheet.

Bill

"SEF" wrote:

> 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.
 |  Next  |  Last
Pages: 1 2
Prev: VBA code to protect cells Outside a given range
Next: Turn a range of cells into 'read only'