if(and(isblank( formula help
 

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),ISBL ANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
but it doesn't work for all cases

if(and(isblank( formula help
 

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,"")

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JC" wrote:

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),ISBL ANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
but it doesn't work for all cases

if(and(isblank( formula help
 

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


Replace @mailinator.com with @tiscali.co.uk


"JC" wrote in message
...
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),ISBL ANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
but it doesn't work for all cases