Ask Your Question

Revision history [back]

Good question!

Let's assume you have a list of names in column A, and we would like the cells C1 and C2 to provide drop-down menu of names (in C1 of all names, in C2 of all names except for the value of C1).

Firstly set the data validation criteria of C1 to the cell range A:A. Then you could fill column B with =IF(EXACT(A1,$C$1),"",A1), which would be every value of A, except for the one selected in C1. Finally set the data validation criteria of C2 to the cell range B:B.

The cons of this way of doing it is that you need a new column for every drop down list, and the drop down lists have to be used in order: first C1, then C2. (This last issue could probably be fixed by comparing the list of available names against every cell that requires data validation except for the current one.)