if(and(isblank( formula help

[JC]

I have columns data
ie:
ad ae ag ah
12 23 34.00 0
result should be
"" "" "" ""
12 blank blank blank
result should be
"" 1 1 1
blank 23 blank blank
result should be
1 " " 1 1
25 blank
blank 12
blank blank blank blank
result should be
" " "" "" ""
i have formula to validate each column and it is supposed to return a 1 if
any of the cells are blank???
=IF(AND(ISBLANK(Data!$AD4),ISBLANK(Data!$AE4),ISBLANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
but it doesn't work for all cases

 

[John Bundy]

Took me a minute to decipher that do you have an example of where it is
not working? my guess is that you need to trim each cell to trap for spaces
(technically a space in a cell is not blank)

=IF(AND(ISBLANK(Trim(Data!$AD4)),ISBLANK(trim(Data!$AE4)),ISBLANK(Trim(Data!$AG4)),ISBLANK(Trim(Data!$AH4))),1,"")

 

[Sandy Mann]

Do you mean something like:

=IF(ISNUMBER(AD4),"",1)

copied (and referencing thecorresponding cells) to columns AE, AG & AH

If the cells may have something other than numbers of blanks in them then
something like:

=IF(ISNUMBER(AD4),"",IF(AD4="",1,"Not blank or a number"))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk