Removing used item from dynamic drop down menu

**XeRo13g** · 10-16-2010, 02:13 AM

Hello, I'm new to the forums and I hope this post isn't in the wrong section.

I have a dynamic list that needs to be validated in another sheet (same workbook) in three different ranges eg. (b3:b15) (j3:j15) and (p3:p15).

The dynamic list contains options that should not be used more than once.
I'm unable to figure out how to make the drop down list to remove any used items.

I would greatly appreciate any solution and thank you in advance!

**DonkeyOte** · 10-16-2010, 05:50 AM

One possibility would be to have 2 lists - one lists all possible entries the other modifies the first list based on selections made thus far

To outline above by means of very simple example (which hopefully mirrors your set up in simplistic form):

Please Login or Register  to view this content.

the above represents all possible items.

In Sheet2!D1:D2, Sheet2!F1:F2 & Sheet2!H1:H2 we will have Data Validation with each cell sharing the same Source list.

The Source List will of course be Sheet1!B1:B6 rather than Sheet1!A1:A6.

To generate the second list:

Please Login or Register  to view this content.

Next we create a Dynamic Named Range such that the final list includes only valid entries

Please Login or Register  to view this content.

Finally we go to Sheet2 and create our DV cells - we select D1:D2, F1:F2 & H1:H2 and apply the following Validation rule:

Please Login or Register  to view this content.

You will find that as you make selections in these cells the options available in the remaining cells become more and more restricted.

I have attached a working example of the above.

**XeRo13g** · 10-16-2010, 12:16 PM

Awesome! Thank you very very much!

I've replaced the $A$1:$A$6 range with the name of my dynamic list and expanded the B$1:B1 range down enough (not sure if this can be automatically along with the dynamic list) and it works as intended!!

I still face a slight problem with the location of my dynamic list. The list begins at A6 and when I try to adapt those formulas, the results get seriously mixed up. (eg In the example you gave, moving down 1 row, b2 will return the value of a3 and so on...)

Is there any way around this, so I wouldn't have to rearrange the whole sheet?

**DonkeyOte** · 10-16-2010, 12:48 PM

Could you possibly post an example that illustrates the ranges in use ?

I appreciate the initial listing commences in A6 and that this is named - but we don't know as yet where the second list is being placed nor the name of the first.

**DonkeyOte** · 10-16-2010, 12:58 PM

I figure a revised sample might be more useful... the original was pretty limited in scope given the formulae used - hopefully this will be a little easier to adapt.