How to convert (IF AND statement) to Custom - Data Validation

love_guy_1977

Board Regular
Joined
Aug 5, 2006
Messages
111
Hi,,

How can I convert (IF AND statement) to Custom - Data Validation.

My Example is:
=IF((AND(A1<>"",B1="")),,"XXX")

It is actually forcing C1 to accept value if A1 is not empty and B1 is empty


Thank you
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Do A1 or B1 have formulas or do you type in value in those cells? It matters.

If they have formulas, then try this as the DV formula...
=AND(LEN(A1)>0, LEN(B1)=0)

Also, make sure you have "Ignore Blank" unchecked on the Data Validation dialog.
 
Last edited:

love_guy_1977

Board Regular
Joined
Aug 5, 2006
Messages
111
Do A1 or B1 have formulas or do you type in value in those cells? It matters.

If they have formulas, then try this as the DV formula...
=AND(LEN(A1)>0, LEN(B1)=0)

Also, make sure you have "Ignore Blank" unchecked on the Data Validation dialog.

Thank you,

=AND(NOT(ISBLANK(A1)), ISBLANK(B1))
is working with "Ignore Blank" unchecked

Thank you again
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,437
Members
409,877
Latest member
DDhol
Top