To update the current name cell you might try something like this:

Sub UpdateName()
Dim CurrName As String
Dim CurrIdx As Integer
CurrName = Range("CurrentName").Value
If CurrName = "" Then
CurrIdx = 1
Else
On Error GoTo BadMatch
CurrIdx = Application.Match(CurrName, Range("NameList"), False)
CurrIdx = CurrIdx + 1
If CurrIdx Range("NameList").Columns.Count Then
CurrIdx = 1
End If
End If
ResumeHe
Range("CurrentName").Value = Range("NameList").Cells(CurrIdx).Value
Exit Sub
BadMatch:
CurrIdx = 1
Resume ResumeHere
End Sub

Assign the name "CurrName" to cell C2 and "NameList" to D11:G11 using Define
Name (Ctrl-F3).

--
Jim Rech
Excel MVP