Hi sherylt13
Here's a different approach...notice the new Sheet "Lists". It contains a Dynamic Named Range called "Dept"...you can call it what ever you wish. It's used to populate the Drop Down in Cell BS3 of Sheet "RosterCurrentLast". Add as many items to it as you wish.
This "Change Code" is in Sheet "RosterCurrentLast" Code Module. As Cell BS3 changes so does Data in Columns BT and BU.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Dim Rng As Range
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$BS$3" Then
For Each cel In Range(("BS4"), Range("BS4").End(xlDown))
Set Rng = Sheets("Guidance Look Up").ListObjects(Target.Value).ListColumns(1).DataBodyRange.Find(cel.Value, , xlFormulas, xlWhole, xlByRows, xlNext, False)
Application.EnableEvents = False
If Not Rng Is Nothing Then
cel.Offset(0, 1).Value = Rng.Offset(0, 1).Value
cel.Offset(0, 2).Value = Rng.Offset(0, 2).Value
End If
Application.EnableEvents = True
Next cel
End If
End Sub
Let me know of issues.
Bookmarks