IF statement on data Validation

Hi,

Can anyone help me on how to place if statement in Data Validation. In allow Validation Criteria, which part I need choose. e.g Decimal, whole number, list, etc


basically the data as follow

A1,A2,A3,A4 Validation criteria, List B1:B5

If A1 selected, A2,A3,A4 must be empty, but if A2,A3,A4 selected, A1 must be empty

Regards,
Farid

Hi Farid,

If I've understood your requirements correctly, you want each cell to have data validation so that only items contained in the list can be selected. However, if A1 isn't blank, then data validation shouldn't let anything be entered in A2 - A4 and vice versa.

The way I've done that is to create two more lists. That perform the "if" part of the validation.

Cell A1 uses the list in C1-C5. If A2-A4 are blank, this list is the same as B1-B5. If they're not blank, then C1-C5 are blank. note that this contains an array formula, so you must press CTRL+Shift+Enter instead of pressing enter to submit this formula =IF($A$2:$A$5="",B1,"")

A2-A4 use the list in C1-C5, which is blank if A1 isn't blank.

Hopefully this will make sense once you take a look at the attached spreadsheet.

Larena