why is my blank cell not a blank cell
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 |
All times are GMT +1. The time now is 04:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com