Forum Discussion
How to Hide Previously Used Item(s) In Dropdown List?
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.
=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.
- 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.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.
