Ask Your Question

discard values from data validation drop down list

asked 2018-05-26 03:09:42 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-06-01 13:22:15 +0100

hjek gravatar image

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.)

edit flag offensive delete link more



Thank you. It is exactly what i need as a value from the drop down list cannot be used twice.

nad gravatar imagenad ( 2018-06-03 01:40:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-26 03:09:42 +0100

Seen: 247 times

Last updated: Jun 01 '18