+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

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

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    04-01-2014
    Location
    Two Rivers, WI
    MS-Off Ver
    Excel Professional Plus 2010
    Posts
    20

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

    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.
    Last edited by joplrw10; 05-09-2016 at 08:19 PM. Reason: more information.

  4. #4
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    610

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

    Something like this...

    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-03-2014, 02:37 AM
  2. [SOLVED] VBA - Distinguishing when a cell is edited by a data validation list or free fromat text e
    By JLD1616 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-24-2014, 10:25 AM
  3. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  4. Auto-Populate cells based on data validation and free-form entry
    By Tawana127 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-17-2013, 02:32 PM
  5. Replies: 0
    Last Post: 10-06-2011, 07:03 AM
  6. Data Validation - List OR Any entry
    By Lassie in forum Excel General
    Replies: 1
    Last Post: 01-01-2011, 11:36 PM
  7. Data Validation > List > Default Entry?
    By BuRn3R in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2009, 06:49 PM
  8. [SOLVED] Stopping free text entry in validation cell
    By smf in forum Excel General
    Replies: 2
    Last Post: 12-15-2005, 03:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1