text as 0

R

Rick Stanford

I have cells formatted as 'text' in several worksheets linked to a text cell
on a 'master' worksheet. When I enter text in the cell on the master
worksheet, it is copied into the cells on the linked worksheets. However,
when I delete the text in the cell on the master worksheet, I get a '0' in
the cells on the linked worksheets - rather than a blank.

How can I format the cells in the linked worksheets to just show a blank,
rather than a '0'?

Thanks,
Rick
 
P

Peo Sjoblom

You can use a custom format like

General;-General;

or you can change the link to

=IF('Master'!A1="","",'Master'!A1)
 
S

Sharon

Or, if you don't need to see any zeros at all, you can click on the Tools
menu > select Options > View Tab and uncheck the Zero Values option.
 
G

Gord Dibben

And more yet.......

Method 1. Hide the zeros using Tools>Options>View. Uncheck "zero values"

Method 2. Use an IF formula in the linked cells.

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

If you have a great whack of these, after you paste the links, run this macro
on the selected range.

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


Gord Dibben Excel MVP
 
Top