Data Validation to Prevent Duplicate Entry from List

[dparteka]

I have two cells that have the same entry possibilities from a validation list. I’m looking to avoid having the same entry in those two cells. Additional explanation is included in the attached workbook. Thank you for looking and the help is really appreciated.

 

[Chihiro]

What's pw for protection?

At any rate, it'd be difficult to do via data validation. Easiest way to do it is in Worksheet_Change event and check if K = N, if it is clear value from cell.

 

[bosco_yip]

One way,

1] Set up a "CSE (Ref)" list in AK1:AM3

2] N2, Data Validation >>

Allow : List

Source : =OFFSET($AK$1,1,MATCH($K2,$AK$1:$AM$1)-1,2)

>> OK >> copied down

So, if user in K2 select "EMPLOYEE" from the dropdown list, N2 only allow to select "SUPPLIER" or "CUSTOMER"

Regards
Bosco

 

[dparteka]

Thank you for your suggestions. I've been playing with this a bit because I'd rather not have a separate list or table to do this and the Worksheet_Change event I'm not sure about. So, I composed the data validation formula below which does work for "CUSTOMER" and "EMPLOYEE" because they are directly above or below each other in the table, but "SUPPLIER" is in the middle resulting in a separation within the table between ""CUSTOMER" and "EMPLOYEE".

CSE table in the "Lists" tab
B2 CUSTOMER
B3 SUPPLIER
B4 EMPLOYEE

This works but for the last one "SUPPLIER" it should be B2 & B4 (skipping over B3)
=IF($K2="CUSTOMER",Lists!$B$3:$B$4,IF($K2="EMPLOYEE",Lists!$B$2:$B$3,IF($K2="SUPPLIER",Lists!$B2:$B4)))

So, the question... is it possible to call out the list and ignore B3 or specify only B2 and B3 in the formula?