How to write an EMPTY cell?

Another option is to use format|Conditional formatting. Make the font match the
fill if the value is 0. (white on white?).

Or maybe you could adjust the subsequent formulas:

Instead of =a1+b1+c1, you could use: =sum(a1:c1)

Instead of =A1+d1+g99, you could use: =n(a1)+n(d1)+n(g99)



Frank News wrote:
>
> Nope. I do need the cell to be NULL or EMPTY. The cells are formatted as
> Numeric with 2 decimals and I can't change that either.
>
> I am also surprised there seems to be no way to do this. There are many
> references to empty cells but they really mean one that just looks empty.
>
> Frank Livni
> frank@mstate.com
> Multi-State Systems, Inc.
> (916) 966-1519
>
> "Alan" <alan@alan.alan> wrote in message
> news:umeatp7dFHA.612@TK2MSFTNGP12.phx.gbl...
> > "Frank News" <newsreply@mstate.com> wrote in message
> > news:%238Y4Rk7dFHA.3880@tk2msftngp13.phx.gbl...
> >>
> >> As part of a formula (or as part of Copy->PasteSpecial, I need to
> >> overwrite a column of cells with either data or leave empty.
> >> Example, =(if a<>b,c,"") - where c is a number - doesn't work
> > because
> >> it puts a blank in the cell.
> >> Example, =(if a<>b,c,0) - where c is a number - doesn't work because
> >> it puts a zero in the cell.
> >>
> >> I don't want to use Tools->Options to show the cells as empty rather
> >> than
> >> 0.00 because that will be so for the whole sheet, not just the one
> >> column I am worrying about.
> >>
> >> I am trying to update a protected sheet automatically from a second
> >> sheet and only the 2 columns I am supposed to enter data into are
> >> unprotected (can be changed). I have the whole thing working now
> >> with a macro except for the cells which are supposed to remain
> > empty.
> >> If I use "" then it messes up a subsequent formula, and if I use 0
> >> then it looks wrong (I want to place the value in each cell of the
> >> column only if it meets a certain condition, else leave it empty.
> >>
> >> Is there a way to make a cell empty as opposed to just looking
> > empty?
> >> I tried to replace all 0 with <delete key> but that didn't work ...
> >>
> >> Looking for ideas ...
> >>
> >> TIA
> >>
> >> Fran
> >
> > Hi Frank,
> >
> > If I understand correctly, then you want a function to return a NULL
> > value.
> >
> > Unfortunately, no such worksheet function exists. This has been, in
> > my opinion, a serious omission in excel for many years.
> >
> > As you correctly point out, an empty string ("") is not the same thing
> > as nothing or a NULL value, and nor is the numeric value zero.
> >
> > As a workaround, could you use 'Not applicable' perhaps?
> >
> > =(if a<>b,c,NA())
> >
> >
> > This works well in charting situations for example.
> >
> >
> > HTH,
> >
> > Alan.
> >
> >

--

Dave Peterson

My problem is that the worksheet is protected and I cannot change anything
else on it.
It was built for someone to enter the data manually - but I have about 2000
rows of data, with only some rows changing on a daily basis, and want to
automate the process as it is not only time-consuming, but probe to errors
if manually updated. It is a column of prices that change. The subsequent
formulas will still work if I use if(a<>b,c,0) and then go to
Tools->Options->Zeroes and uncheck display zeroes. Then the sheet looks
fine (even though the cells may contain zeroes). I am not happy about that
as it will also suppress the zero display in other parts of the sheet.

Can't change format - it is locked.

Frank

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42BA955C.1732BEF6@netscapeXSPAM.com...
> Another option is to use format|Conditional formatting. Make the font
> match the
> fill if the value is 0. (white on white?).
>
> Or maybe you could adjust the subsequent formulas:
>
> Instead of =a1+b1+c1, you could use: =sum(a1:c1)
>
> Instead of =A1+d1+g99, you could use: =n(a1)+n(d1)+n(g99)
>
>
>
> Frank News wrote:
>>
>> Nope. I do need the cell to be NULL or EMPTY. The cells are formatted
>> as
>> Numeric with 2 decimals and I can't change that either.
>>
>> I am also surprised there seems to be no way to do this. There are many
>> references to empty cells but they really mean one that just looks empty.
>>
>> Frank Livni
>> frank@mstate.com
>> Multi-State Systems, Inc.
>> (916) 966-1519
>>
>> "Alan" <alan@alan.alan> wrote in message
>> news:umeatp7dFHA.612@TK2MSFTNGP12.phx.gbl...
>> > "Frank News" <newsreply@mstate.com> wrote in message
>> > news:%238Y4Rk7dFHA.3880@tk2msftngp13.phx.gbl...
>> >>
>> >> As part of a formula (or as part of Copy->PasteSpecial, I need to
>> >> overwrite a column of cells with either data or leave empty.
>> >> Example, =(if a<>b,c,"") - where c is a number - doesn't work
>> > because
>> >> it puts a blank in the cell.
>> >> Example, =(if a<>b,c,0) - where c is a number - doesn't work because
>> >> it puts a zero in the cell.
>> >>
>> >> I don't want to use Tools->Options to show the cells as empty rather
>> >> than
>> >> 0.00 because that will be so for the whole sheet, not just the one
>> >> column I am worrying about.
>> >>
>> >> I am trying to update a protected sheet automatically from a second
>> >> sheet and only the 2 columns I am supposed to enter data into are
>> >> unprotected (can be changed). I have the whole thing working now
>> >> with a macro except for the cells which are supposed to remain
>> > empty.
>> >> If I use "" then it messes up a subsequent formula, and if I use 0
>> >> then it looks wrong (I want to place the value in each cell of the
>> >> column only if it meets a certain condition, else leave it empty.
>> >>
>> >> Is there a way to make a cell empty as opposed to just looking
>> > empty?
>> >> I tried to replace all 0 with <delete key> but that didn't work ...
>> >>
>> >> Looking for ideas ...
>> >>
>> >> TIA
>> >>
>> >> Fran
>> >
>> > Hi Frank,
>> >
>> > If I understand correctly, then you want a function to return a NULL
>> > value.
>> >
>> > Unfortunately, no such worksheet function exists. This has been, in
>> > my opinion, a serious omission in excel for many years.
>> >
>> > As you correctly point out, an empty string ("") is not the same thing
>> > as nothing or a NULL value, and nor is the numeric value zero.
>> >
>> > As a workaround, could you use 'Not applicable' perhaps?
>> >
>> > =(if a<>b,c,NA())
>> >
>> >
>> > This works well in charting situations for example.
>> >
>> >
>> > HTH,
>> >
>> > Alan.
>> >
>> >
>
> --
>
> Dave Peterson