Dependent drop down lists on a worksheet

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

Rich (BB code):
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,293
Members
414,223
Latest member
Accountant2B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top