Dynamic Dependant Drop Down Lists?? Please Help!!!

[HTSNY_JACK]

Hi - I am trying to fine-tune a scheduling spreadsheet for my company's 2014 work. We have projects organized by specific areas, with varying numbers of projects in each area. My goal is to organize the spreadsheet so i can select an area from a drop-down list in column A, which will result in a corresponding set of options to select from in a drop-down list in column B. I have my spreadsheet set up this way, so far. Once the selection is made in column B, the rest of the row is populated with corresponding data.

The function that i would like to incorporate is this: if there are 3 jobs in area A, and i select job 1 in row 7, for example, when i select area A in row 8, how can i set things up so that the only options that i am shown in the drop-down list are jobs 2 and 3? In other words, as we work through our 2014 schedule (the workload is static, there will not be new jobs added throughout the year) and i plan out our schedule a couple weeks ahead of real-time, there will be fewer and fewer job options available to select because those jobs will have already been selected and scheduled.

I followed a few google search leads for "dynamic drop down lists," but couldn't find anything to explain exactly what i would like to do. I'm not even sure if it's possible, but with everything i've learned about excel (lots from this message board; i received a very informative and helpful answer to a question posted last year, thank you very much!) i believe that ANYTHING is possible. Any help would be HUGELY appreciated! Thank you very much!!

-Jack

 

[BobUmlas]

Should have searched for "cascading validation lists".
See Excel-User.com: Cascading Validation Lists

 

[HTSNY_JACK]

Thanks for the quick reply! I looked into that page and went through the demonstration steps to create a spreadsheet that works according to the description. I am able to get that same effect in my spreadsheet through the =INDIRECT and =LOOKUP, but I would like to describe my situation a little more specifically and hopefully you can point me in the right direction.

My spreadsheet starts with 2 columns: "Consoles" and Circuits." Let's say that I have 3 "Consoles:" "Red," "White," and "Blue." In each of those Consoles, there are 5 "Circuits:" R1-R5, W1-W5, etc. When I pick my first job, i select "Red" from the Console drop-down list in column A, Row 2, and then I pick R1 from the resulting drop-down list in column B, Row 2, because the =INDIRECT and =LOOKUP functions have found the "Red" circuits and populated the drop-down list according to my column A selection. I believe we're on the same page so far.

Now, when I go to select my NEXT job in row 3, if i want to do another "Red" console circuit, i will select "Red" in column A. When I click on the resulting drop-down list in column B, how do i make that list show "R2-R5" because R1 has already been selected? Currently, since the corresponding list is R1-R5, I could select the same circuit over and over and over again because my list of options never changes. I would like that list of options in column B to eliminate previously selected circuits, as i move down the spreadsheet, such that if i decided to do all the Red circuits in a row and my first 5 jobs are R1, R2, R3, R4 and R5 in rows 2-6, if i were to select "Red" in column A, row 7, for my 6th job, the resulting drop-down list in column B will be empty since all 5 of the possible choices have already been selected.

What would this function be called? Thanks a lot for the help!

-Jack