I must fill 100 cells with names from a list.
I use data validation.
Is it possible to discard from the list names that i have already selected in previous cells?
I would like to do it even with complex formula or macro.
Exemple
List of names
A
B
C
D
In first cell, i select B.
When I open the drop down in second cell, i would like to see only A, C and D.
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.)
Thank you. It is exactly what i need as a value from the drop down list cannot be used twice.