paste linked cells show "0"

[Doug Howell]

I have a number of cells in a range (lets say A14:J52).
Some of these cells are "paste linked" from cells on other sheets.

Unfortunately, when the original cell is empty, the value of the
"paste linked" cell shows as "0".
(not just blank like the original)

Is there a way to keep the "paste linked" cell showing "0"?

If not, how would I go about using VBA to systematically go through
the above described range and delete those zeros?

At the point I ran this code, any "0" could be deleted from the range
safely whether it was a "paste linked" cell or not.


Any help would be appreciated.


Doug

 

[muddan madhu]

format the cell as #

 

[Doug Howell]

Changing the cell to number doesn't work.
It still shows "0" in the linked cell.

 

[Gord Dibben]

Format as Custom

Type the # sign only


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Too fast with the send button.

The custom format of # will hide the zeros but not show decimals if your
blank source cell becomes a number like 123.45

Rather than pasting links it is better, not easier, to use a formula like

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


Gord

 

[AB]

Tools>Options>View>Zero Values.

That affects any zero, though - even the legitimate ones.
Can also use Conditional formatting - font & Cell the same colour
(white?).

 

[Doug Howell]

For Excel 2007, this is actually easier....

Excel Options -> Advanced -> Display Options for this Worksheet
uncheck the "show a zero in cells that have zero value" box