Data Validation with Free Entry, but needs to update Validation List with new value

**joplrw10** · 05-09-2016, 07:37 PM

I have looked high and low for something relating to this, but have come up with little.

Unsure whether to post this in functions or VBA since there may very well be answers in both.

Current situation. I have a data set that is being used as a Data Validation forced input for a spreadsheet. However, all the possible entries to the sheet are not in the data validation set initially, and need to be added as they are entered.

The setup has my Data Validation table. The data is set up as a table in a separate sheet. Utilizing a table, the entries are able to expand as I need them by referencing the table.

My question is, if a free text is entered into a cell with a data validation drop down menu, how can I have it append to the end of the table if it does not already exist?

Current example is attached.

Thank you very much.

**Richard Buttrey** · 05-09-2016, 08:14 PM

Maybe I'm missing something here but the point of a data validation is to prevent a user entering stuff that's outside predefined constraints.

i.e. you can't enter free text if you are using data validation.

Would you clarify please.

**joplrw10** · 05-09-2016, 08:18 PM

Of course,

The end result required is a drop down box that allows for free entry. If an item is entered instead of chosen from the drop down box, I need it to add itself to the possible options that can be chosen from the dropdown list.

I used Data validation because I know that I can create a drop down box using that without having to go into ActiveX or Form controls. I have turned off the error function of data validation to allow for free entry.

If there is an easier way that I am unaware of, I am happy to use it.

**spitfireblue** · 05-09-2016, 08:27 PM

Something like this...

Please Login or Register  to view this content.

Data Validation with Free Entry, but needs to update Validation List with new value

LinkBack

Thread Tools

Rate This Thread

Display

Data Validation with Free Entry, but needs to update Validation List with new value

Re: Data Validation with Free Entry, but needs to update Validation List with new value

Re: Data Validation with Free Entry, but needs to update Validation List with new value

Re: Data Validation with Free Entry, but needs to update Validation List with new value

Thread Information

Users Browsing this Thread

Similar Threads

Data Validation - How to update cells when a name is selected from a Data Validation List

[SOLVED] VBA - Distinguishing when a cell is edited by a data validation list or free fromat text e

Data validation choice in one cell FORCES entry from data validation list in another

Auto-Populate cells based on data validation and free-form entry

Manual entry error with 2 column data validation. Drop down list entry works fine.

Data Validation - List OR Any entry

Data Validation > List > Default Entry?

[SOLVED] Stopping free text entry in validation cell

Bookmarks

Bookmarks

Posting Permissions