How to write an EMPTY cell?

[Frank News]

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

 

[Alan]

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.

 

[Frank News]

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
(e-mail address removed)
Multi-State Systems, Inc.
(916) 966-1519

 

[Biff]

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

 

[Dave Peterson]

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]

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

 

[Harlan Grove]

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.

 

[Frank News]

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

Frank