How to Hide Previously Used Item(s) In Dropdown List?

[Ron_2510]

I have sheet that makes certain Data Validation Lists available based on how the source cell is populated. Their are three dropdown lists that can be used. Here is a sample of the DataValidation formula, =IF(A$3="ACME",Lists!$G$2,IF(A$3="Roadrunner",Lists!$H$2:$H$3,IF(A$3="Coyote",Lists!$I$2:$I$3))). Each list has one or two dropdown selections based on what A3 is.

How to hide/remove the choice from any other cells on the sheet, with the dropdown once it is used. There are many cells that have the dropdown on the main sheet. However once the selection is used there should be a blank selection anywhere else, unless that item is cleared, to be used in another cell on that sheet.

* A3 has a Data Validation list to set the base (ACME with one option, Roadrunner with two options, Coyote with two options)

* A3 sets the base for the rest of the cells that have the option to use a dropdown on that sheet.

* Each of those cells have the dropdown list with options. ex: ACME is A3, the Dropdown will only have ACME. If Roadrunner was chosen for A3, the options are Roadrunner#1 or Roadrunner#2.

* How do you hide a choice, once used?

[#2]

I would take your logic out of the data validation and into a helper column. So let's say in A4 you say:
=IF(A$3="ACME",Lists!$G$2,IF(A$3="Roadrunner",Lists!$H$2:$H$3,IF(A$3="Coyote",Lists!$I$2:$I$3)))
then in B4 you can create a filtered list:
=FILTER(A4#, countif( [dropdowns], A4#)=0, "")
Then the data validation formula is =B4#
the [dropdowns] is the range of your dropdowns and if they aren't in a single range I would make a single range that references each of those locations.
You could also combine some of these steps using LET() and other new formulas if you needed to.

[#3]

@mtarler : Ok. thanks. I'll take a look at that.

[#4]

I may not have explained this correctly.
A3 has a dropdown list to choose from (companies), ACME, Roadrunner or Coyote.
The other cells (many) use a data validation dropdown list built with this formula, =IF(A$3="ACME",Lists!$G$2,IF(A$3="Roadrunner",Lists!$H$2:$H$3,IF(A$3="Coyote",Lists!$I$2:$I$3))). Each company then has one or two floating holidays, ACME#1, Roadrunner#1 or Roadrunner#2 and Coyote#1 or Coyote#2.
If the company (A3) is ACME then the other cells dropdown only has one choice, (ACME#1) for a floating holiday. once that is used all the other days should only get a blank in the dropdown.

If the Company (A3) is say, Roadrunner the dropdown now has Roadrunner#1 and Roadrunner#2.
Again the challenge, once Roadrunner#1 and or Roadrunner#2 is used it should be hidden so it can not be used twice.

[#5 · Accepted solution]

@Ron_2510  I think I understood.  I have attached a sample sheet using the formulas I mentioned except I moved the 'helper lists' down to row 10 instead of A4 and B4 to give some space.  They can really go anywhere.  As I noted if the additional drop downs are 'together' you can reference that range but if not, I suggest you create another set of helper cells.  For example if your other drop downs are in C5 and E7 then in the helper list use the formula =C5 and =E7 so in the COUNTIF formula inside the FILTER formula you can reference a single range as I demonstrate in the attached.  

[#6]

@mtarler how do I post the file i'm working on?  Here are screen shots.

[#7]

You go into "Open full text editor" and there is a box to add an attachment. If it isn't letting you post here then you can post a link to the file in sharepoint or dropbox or the like or you can PM me here (click my name and then message).
Either way, did you open the file I sent? Do the Yellow drop downs work the way you want? On your sheet you want the dropdown options to be exclusive per 2wk period (e.g. D10:D25) or additional days/weeks (i see another section in column S and are there more?)

[#8]

ok. I was over thinking your example sheet. I swapped the formulas around. It all works the way it should. THANK YOU!!!! It was driving me crazy!!!