Update Validation List

I've created multiple ranges in a sheet named ' Schedules' that all use one Data Validation list. The list source is a Named Ranges called 'AbbNames', which points to a column ($S) on another sheet named 'Students'.

AbbNames is simply a list of student names--that are values; AbbNames does not contain a formula. However, AbbNames was created with VB code in the Worksheet Change Event. The code is:



Dim rng As Range
Dim rng1 As Range
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set rng = Target.Dependents
On Error Goto 0
If Not rng Is Nothing Then
Set rng1 = Union(rng, Target)
Else
Set rng1 = Target
End If
On Error Goto errHandler
If Not Application.Intersect(rng1, _
Application.Range("Abbreviated")) Is Nothing Then
Application.EnableEvents = False
Application.Range("AbbNames").Value = _
Application.Range("Abbreviated").Value
End If
errHandler:
Application.EnableEvents = True
If Err.Number <> 0 Then _
MsgBox "Error occured " & Err.Number & vbNewLine & _
Err.Description

Note that students' names were originally concatenated in a column named 'Abbreviated' located in column G.

(this code creates the 'AbbNames' data in column S and updates the data if data is changed in 'Abbreviated'--if data is originally changed in columns A and B, which are firstname and lastname)

To make a long story shorter, I needed a column of concatenated names that were values--not calculated--to base the listboxes off of in the Schedules sheet. Validation list boxes can't be based off of calculated columns, right??

However, the listboxes do NOT work. When a user clicks on the drop-down arrow, Excel pauses for about 20 seconds (the initial time) and then shows nothing in the list except a blank field. When the blank list is clicked, the top name from 'AbbNames' is entered. However, no names can be seen or selected in the list box.