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.
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.
- 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?)- 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!!!