PLEASE HELP !!! Dropdown list dependent on another ...
Posted by Maureen Hill on November 19, 2001 4:59 AM
If anyone could help me i would greatly appreciate it. I was offered a potential solution to a problem I'm having ... namely I want to select the contents of the first column in a spreadsheet from a drop down list (list stored in another sheet in the same workbook). Then, the contents of the second column need to be selected from another drop down list (stored in another sheet in the same workbook) BUT the list that drops down in the second column needs to be dependent on what was chosen from the drop down list in the first column. It was suggested that I use the =INDIRECT function in the data validation field, but this is returning me an error "The source currently evaluates to an error." I'M PULLING MY HAIR OUT and this was due two yesterdays ago ... Please help if you can.
Posted by Rick E on November 19, 2001 6:07 AM
Re: Dropdown list dependent on another ...cell link
Here is what I would do. Have the cell link of the first dropdown point to the sheet and cell that has the second dropdown's lists (call it sheet S2) have the second dropdown "point to" a fixed area that will get "loaded" or "updated" based on the first link cell value (like A5:A25). Use the "Worksheet_Change" function of S2 to move the correct set of lists to A5:A25 of that sheet when the cell link changes.
Posted by Mudface on November 19, 2001 6:20 AM
Posted by Mudface on November 19, 2001 6:26 AM
I'm just in the process of doing a similar thing myself. In my case I need to have one list with general work areas where things are stored and a second dependent list of a specific location within the general area. I found the following works: -
In the second validated column (B), put the validation as =INDIRECT($A1). Then name each of your second set of validated lists with a name from the first list. For example, if 'Research_Lab' is selected in my column A, column B then looks for the range named 'Research_Lab' and offers a choice of bench nos in the drop-down list and so on.
