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