×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to automatically update a validated list

How to automatically update a validated list

How to automatically update a validated list

(OP)
Can anyone tell me how to update a validated list automatically just by entering new data into the cell?
Example: If I have a list called servers, how can I add another server to that list?

Thanx

RE: How to automatically update a validated list

David,

The best way to do this (if you are entering new items on the worksheet manually) is to make sure you are within the current boundary of the named range ("Servers" in your example), right-click, select Insert... then Move cells down in the dialog.  This will extend your named range automatically.  Enter the new item in blank cell and the validation list will contain the new item.  This makes it easy to place a new item into an existing order.  The only tricky situation is if you want to add the new item to the bottom of the list.  Select the current last item, insert a new cell as before, move the last item up into the blank cell then add the new item.


Regards,
Mike

RE: How to automatically update a validated list

You can save a step in the manual insertion by copying the last element and doing a "insert copied cells.." and the pasting the new item into the last cell.

TTFN

RE: How to automatically update a validated list

(OP)
Okay...so I have created a data validation sheet and I have it hiden with'in the workbook.
What I would like to be able to do is add to this list from the worksheet itself and have those entires update the hiden data sheet. This way I don't have to hide and unhide the sheet when I need to add something.

Can anyone help me with coding this?

Thank you so much

David Morrison

RE: How to automatically update a validated list

David,
My validation lists are on the same worksheet as my data but this is how I did it, but I have to click on the button to run it.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.Insert Shift:=xlDown
newvar = InputBox("Enter your new variable here"):
ActiveCell.FormulaR1C1 = newvar
Range("C5").Select
ActiveCell.FormulaR1C1 = newvar
End Sub

my list starts in cell F4 and the calling cell is C5.
if someone else can do it without clicking the button I will change my code...

Todd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources