Data validation formula

Mlowe3

New Member
Joined
Jan 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I set To see your Data Validation List at B2, & After A2 copied to Lastrow at Column C, Deleted.
Try this
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,076
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top