"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.