Hi team,

this is probably basic but :

when I have a formula in a cell it is then deemed as not blank for when I am
making refereneces to it or it's range, #n/a - #value etc are returned when i
want it to be treated as blank.

anyone throw some light on this please

thanks

steve

Well - you kinda answered your own question. If it's got a formula in it, how
can it possibly be blank ??

This boils down to how excel views cell contents. You can have 2 types of
cell content - a formula or a value (whether that value be numeric or text).
Whatever type of content, it is still content. The TEXT of the cell may be an
empty string ( "" ) but there is still a formula below it that means the cell
is not empty. This is the crux - the cell is not EMPTY but its text value IS
BLANK. You cannot, however, test for BLANK cells using any of the IS...
statements - ony way to test is to test for a zero length string eg
=if(A1="","Cell has zero length string","Cell has visible text")

OR

=if(LEN(A1)=0,"Cell has zero length string","Cell has visible text")
--
Rgds, Geoff

"A crash reduces
Your expensive computer
To a simple stone"


"steve alcock" wrote:

Hi team,

this is probably basic but :

when I have a formula in a cell it is then deemed as not blank for when I am
making refereneces to it or it's range, #n/a - #value etc are returned when i
want it to be treated as blank.

anyone throw some light on this please

thanks

steve

Hi Geoff,

many thanks for the answer, I am sure not only me has this problem and your
answer certainly explains the difference and how to get round the blank and
not blank senario.

regrads

steve


"xlbo" wrote:

Well - you kinda answered your own question. If it's got a formula in it, how
can it possibly be blank ??

This boils down to how excel views cell contents. You can have 2 types of
cell content - a formula or a value (whether that value be numeric or text).
Whatever type of content, it is still content. The TEXT of the cell may be an
empty string ( "" ) but there is still a formula below it that means the cell
is not empty. This is the crux - the cell is not EMPTY but its text value IS
BLANK. You cannot, however, test for BLANK cells using any of the IS...
statements - ony way to test is to test for a zero length string eg
=if(A1="","Cell has zero length string","Cell has visible text")

OR

=if(LEN(A1)=0,"Cell has zero length string","Cell has visible text")
--
Rgds, Geoff

"A crash reduces
Your expensive computer
To a simple stone"


"steve alcock" wrote:

Hi team,

this is probably basic but :

when I have a formula in a cell it is then deemed as not blank for when I am
making refereneces to it or it's range, #n/a - #value etc are returned when i
want it to be treated as blank.

anyone throw some light on this please

thanks

steve