Home |
Search |
Today's Posts |
#1
|
|||
|
|||
text as 0
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 |
#2
|
|||
|
|||
You can use a custom format like
General;-General; or you can change the link to =IF('Master'!A1="","",'Master'!A1) -- Regards, Peo Sjoblom "Rick Stanford" wrote in message ... 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 |
#3
|
|||
|
|||
Hi Rick
Try formatting cells on the linked worksheets with something like... [0]#,##0_ ;[Red][<0](#,##0); -- ----- XL2003 Regards William "Rick Stanford" wrote in message ... 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 |
#4
|
|||
|
|||
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. "Rick Stanford" wrote: 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 |
#5
|
|||
|
|||
And more yet.......
Method 1. Hide the zeros using ToolsOptionsView. 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 On Mon, 18 Apr 2005 06:44:42 -0700, "Peo Sjoblom" wrote: You can use a custom format like General;-General; or you can change the link to =IF('Master'!A1="","",'Master'!A1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Why is my text changing to pound signs? | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |