How to write an EMPTY cell?

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

"Frank News" <[email protected]> wrote in message
news:%[email protected]...
>
> 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.

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
[email protected]
Multi-State Systems, Inc.
(916) 966-1519


"Alan" <[email protected]> wrote in message
news:[email protected]...
> "Frank News" <[email protected]> wrote in message
> news:%[email protected]...
>>
>> 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.
>
>

Hi!

You said:

> If I use "" then it messes up a subsequent formula

If that's the only reason you don't want to use "" then you should be able
to deal with that in your subsequent formula.

What is that formula? (and be very specific)

Biff

"Frank News" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> Multi-State Systems, Inc.
> (916) 966-1519
>
>
> "Alan" <[email protected]> wrote in message
> news:[email protected]...
>> "Frank News" <[email protected]> wrote in message
>> news:%[email protected]...
>>>
>>> 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.
>>
>>
>
>

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
> [email protected]
> Multi-State Systems, Inc.
> (916) 966-1519
>
> "Alan" <[email protected]> wrote in message
> news:[email protected]...
> > "Frank News" <[email protected]> wrote in message
> > news:%[email protected]...
> >>
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> Multi-State Systems, Inc.
>> (916) 966-1519
>>
>> "Alan" <[email protected]> wrote in message
>> news:[email protected]...
>> > "Frank News" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >>
>> >> 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

Frank News wrote...
....
>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 ...

If you're using macros to fill these cells, you have two alternatives.

cellref.Value = Empty
cellref.ClearContents

Both work just fine on unlocked cells in protected worksheets.

Oh, now that sounds promising! I'll let you know ...
Thanks a bunch.

Frank
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Frank News wrote...
> ...
>>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 ...
>
> If you're using macros to fill these cells, you have two alternatives.
>
> cellref.Value = Empty
> cellref.ClearContents
>
> Both work just fine on unlocked cells in protected worksheets.
>