Data validation formula

[Mlowe3]

I am looking to create a data validation formula. I have a list of names in column C from 3-20.
In A2 I want to put in the name at the top of list (C3) and when I hit enter that name will rotate to the bottom (C20) and the next name moves up. I have the code on the sheet but I can not figure out what to use it works once and when I put the next name in it does nothing.

I know I need a formula so it works with the names in column C I just don’t know how.
Can anyone help?

Thank you
Maria

 

[maabadi]

1. Are you want to Put Name (that Input at A2) Insert at the end of range at column C?
2. What about Macro (VBA)?

 

[maabadi]

I set To see your Data Validation List at B2, & After A2 copied to Lastrow at Column C, Deleted.
Try this

Sub DataValid1()
Dim Lr As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
Range("C" & Lr + 1).Value = Range("A2").Value
Range("A2").ClearContents
With Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=OFFSET(Sheet1!$C$3,0,0," & Lr - 1 & ",1)"
.ErrorTitle = "Date Invalid"
.ErrorMessage = "Enter a valid date or pick from the drop down list."
End With
End Sub