Dependent drop down lists on a worksheet

[jerH]

Some former students have come to me with a question. My instinct would be to have them do their data entry with a UserForm, but I doubt they have time to recode that. Here's their issue:

They are building a course planning worksheet for laying out a four year college plan, checking pre-reqs, etc. They have a list of all available undergraduate courses on a sheet. On their user input sheet, they use data validation to create a drop down that references this list of courses.

Their instructor has asked them to make the input sheet such that once a course is chosen it no longer appears in any of the other drop downs on the sheet.

If we were working with a userForm I could see how we could control the options that appear in a comboBox individually, but is there any good formula magic to handle this case?

I hope this question is clear...please let me know if I can provide any clarifying details.

 

[Eric W]

That can be done. Here's a link to another thread which explains how.

http://www.mrexcel.com/forum/excel-...uplicates-help-visual-basic-applications.html

I think it's clear enough so that you can adapt it to your sheet. I've learned a bit about Excel since I originally wrote that, and I suspect I can make it a bit more efficient. I'll give that a shot when I have a chance.

Let me know if you have any questions.

 

[Fishboy]

Some former students have come to me with a question. My instinct would be to have them do their data entry with a UserForm, but I doubt they have time to recode that. Here's their issue:

They are building a course planning worksheet for laying out a four year college plan, checking pre-reqs, etc. They have a list of all available undergraduate courses on a sheet. On their user input sheet, they use data validation to create a drop down that references this list of courses.

Their instructor has asked them to make the input sheet such that once a course is chosen it no longer appears in any of the other drop downs on the sheet.

If we were working with a userForm I could see how we could control the options that appear in a comboBox individually, but is there any good formula magic to handle this case?

I hope this question is clear...please let me know if I can provide any clarifying details.

Hi jerH,

Assuming that the list of courses can be found in column A of Sheet2 (you can update the column / sheet name in the code to suit), the following Worksheet_Change macro can be applied directly to the back end of the sheet containing the drop down menus. To do this simply right-click on the sheet name at the bottom of the screen and select View Code. In the window that opens copy and paste in my code. This macro will remove the most recently selected option from the source list preventing it from being selected a second time. Be aware however that the removal is final and cannot be undone beyond manually adding the option back into the source list.

Please note that the macro is currently looking at column 1 (A) for changes. You will need to update this if the drop-down menus are not in column A

Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim FindString As String, Rng As Range
    ' If you update a drop-down menu in column A and the value is not blank then...
    If Target.Column = 1 And Target.Value <> "" Then
        ' Set variable FindString as the selected option from the drop-down
        FindString = Target.Value
            ' If FindString is not empty then...
            If Trim(FindString) <> "" Then
                ' Find the value in the source list of Sheet2 column A
                With Sheets("Sheet2").Range("A:A")
                    ' Set Rng as where the value is found
                    Set Rng = .Find(What:=FindString, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                    ' If Rng exists then...
                    If Not Rng Is Nothing Then
                        ' Delete Rng (removing that option) from the source list
                        Rng.EntireRow.Delete Shift:=xlUp
                    ' Else if Rng does not exist then...
                    Else
                        ' Display a message that the value does not exist
                        MsgBox "Nothing found"
                    End If
                End With
            End If
    End If
End Sub