Creating Dependent Drop-Down Lists

[ChryslerPt]

Hello everyone,
I have a quick question that might be simple to figure out for some.

I am trying to get a listbox I created in from DataValidation to be dependent on another list.

For example, when I select Course 1 (a list item) from the Course List drop-down, I want the cost to auotmatically populate in the Cost drop-down (the other list).

How do I get this to happen? Here is the code I created in VB, and I am not sure if this is the right course of action:

Sub Automated()
Sheet1.Cells(2, 1) = "MATLAB"
If (Sheet1.Cells(2, 1) = "MATLAB") Then
Sheet1.Cells(2, 3) = "31"
End If
Sheet1.Cells(2, 1) = "INCA"
If (Sheet1.Cells(2, 1) = "INCA") Then
Sheet1.Cells(2, 3) = "41"
End If

Sheet1.Cells(2, 1) = "TargetLink"
If (Sheet1.Cells(2, 1) = "TargetLink") Then
Sheet1.Cells(2, 3) = "51"
End If

If ((Sheet1.Cells(2, 3) = "0") Or (Sheet1.Cells(3, 2) = 0)) Then
Sheet1.Cells(2, 3) = "3999"
End If

End Sub


I don't want to use the user form, however from VB. I want to use the regular drop-down. The code seems to work, but, each list item in the drop-down is not pulling the data I want it to. I need to activate the drop-down as a click event somehow. It's just recognizing the cell A2 as a whole and not the individual list items. I want to each list item to be their own object, and to automatically populate the cell C2 with their cost when they are selected.

Thanks everyone.

 

[VoG]

This may help you http://www.contextures.com/xlDataVal02.html

 

[ChryslerPt]

Hi VoG,

Thank you for the response. Unfortunately, that is not what I am looking for. It's similiar, but not quite.

If I select a list item from A2, then I want it's cost to automatically pop in cell C2. I want it to be like a quick find.

Is this easy to accomplish or not in Excel?

Thanks again for your response.

 

[VoG]

So, you need VLOOKUP http://www.contextures.com/xlFunctions02.html