Dependent data validation (drop down) with vlookup

[hshamdani]

Hello folks,
I have a list of students and the classes they are taking (Sheet1) and I have a ledger (Sheet2).

Sheet1:

Student ID	Student Name	Class	Total Amount	Total_Paid	Balance
101	Student Name 1	Math	150
101	Student Name 1	Science	150
101	Student Name 1	Geography	80
101	Student Name 1	History	80
101	Student Name 1	P.E.	50
101	Student Name 1	Arts	50
102	Student Name 2	Science	100
102	Student Name 2	Geography	100
102	Student Name 2	History	75
102	Student Name 2	P.E.	10
103	Student Name 3	Math	200
103	Student Name 3	Science	250
103	Student Name 3	Geography	100
103	Student Name 3	History	55
103	Student Name 3	P.E.	50
103	Student Name 3	Arts	50
103	Student Name 3	Biology	80
103	Student Name 3	AP	300

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Sheet2:

S_ID	S_Name	Class	Date	Debit	Credit
101	Student Name 1

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

I have put a vlookup function in the ledger (sheet2) where, if i put the student id the student name populates in the next cell. What i need now is a drop down menu in the column next to it with ONLY the names of classes that respective student is taking. And once a transaction is recorded in the ledger that students payment and balance amount needs to be automatically updated on sheet1.
I hope this explained my problem sufficiently. I am new to this so please bear with me. should there be any question please let me know. thanks

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Apologies in advance, this is going to be slightly complicated but I can't think of another way using Data Validation.

You can use different Lists with Data Validation if you can assign a Defined Name to each one. That would mean creating a different Defined Name for each name in your master list. Defined Names can be created using the Formulas--> Defined Names--> Name Manager menu path.

The second problem is that Defined Names cannot contain blanks (and there are some other restrictions). So those will need to be removed.

My proposal is that you have a macro, which will need to be pasted in to a Macro Module that will create all the Defined Names for you. You will need to run the macro every time you make a change to your master name/course list. (It may be possible to automate that if you decide to go with this proposal.)

The second step is to create a Data Validation rule that will remove the blanks from the names and call up the correct list.

Macro code:

Sub CreateDefinedNames()

    Dim i As Long
    Dim Name As String
    Dim iStart As Long

    With ThisWorkbook.Worksheets("Sheet1")
        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            ' First Name
            If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
                Name = .Cells(i, "B").Value
                iStart = i
            End If
            ' Last Name
            If .Cells(i, "B").Value <> .Cells(i + 1, "B").Value Then
                .Range(.Cells(iStart, "C"), .Cells(i, "C")).Name = Replace(Name, " ", "_")
            End If
        Next
    End With

End Sub

The code runs down the list of names in Sheet1.
It notes the first and last row for each name then sets the range to the list of courses.
It also replaces any blanks in the names with underscores (_).

To call up those Defined Names you need to also remove the blanks there as well. So the formula for cell B2 is:
=INDIRECT(SUBSTITUTE(B2," ","_"))

Once you have set up one cell you can fill down that cell to all the required ones.