Jul 04 2022 12:02 PM
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?
Jul 04 2022 01:25 PM
Jul 04 2022 02:05 PM
@mtarler : Ok. thanks. I'll take a look at that.
Jul 05 2022 05:07 AM
Jul 05 2022 06:59 AM
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.
Jul 05 2022 09:31 AM
@mtarler how do I post the file i'm working on? Here are screen shots.
Jul 05 2022 11:10 AM
Jul 05 2022 11:33 AM
Jul 05 2022 06:59 AM
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.