if(and(isblank( formula help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JC - 22 May 2008 21:06 GMT

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

Reply to this Message

John Bundy - 22 May 2008 21:12 GMT

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

Signature

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

> I have columns data
> ie:
[quoted text clipped - 17 lines]
> =IF(AND(ISBLANK(Data!$AD4),ISBLANK(Data!$AE4),ISBLANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
> but it doesn't work for all cases

Reply to this Message

Sandy Mann - 22 May 2008 23:25 GMT

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

Signature

HTH

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

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

>I have columns data
> ie:
[quoted text clipped - 17 lines]
> =IF(AND(ISBLANK(Data!$AD4),ISBLANK(Data!$AE4),ISBLANK(Data!$AG4),ISBLANK(Data!$AH4)),1,"")
> but it doesn't work for all cases

Reply to this Message