+ Reply to Thread
Results 1 to 8 of 8

Make a Data Validation List Grow With New Entries?

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Make a Data Validation List Grow With New Entries?

    Hello!

    You click a cell (A1)
    The Data Validation dropdown comes up.
    I don't see my item in the list, so I type the data ("dog")into A1.
    I click off that cell.
    If I click back to A1, now "dog" is in the dropdown and that new list will be saved when the workbook closes.


    Can this be done?

    VR/Lost
    Last edited by leaning; 06-30-2010 at 10:03 AM. Reason: Solved!

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Make a Data Validation List Grow With New Entries?

    You need a Dynamic named list

    http://excel-it.com/data_validation.htm

    http://www.exceluser.com/explore/dynname1.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Make a Data Validation List Grow With New Entries?

    Hello!

    Data Validation for A1:

    Allow List
    Source: =Range


    Choices are in Column B.

    Named Range
    Name: Range
    Refers to: =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
    Scope: Sheet1


    When I add "Dog" to A1, the list (Column B) is not being updated.

    What am I doing wrong?

    VR/Lost

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Make a Data Validation List Grow With New Entries?

    It should be,attach the workbook

  5. #5
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Make a Data Validation List Grow With New Entries?

    RoyUK,

    I appreciate you working with me on this!
    Here it is.
    VR/Lost
    Attached Files Attached Files

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Make a Data Validation List Grow With New Entries?

    You can't add an item to A1 & expect it to update the named range in B. You add the items to the bottom of the list in B

  7. #7
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Re: Make a Data Validation List Grow With New Entries?

    Roy,

    Good news! You can!

    I found what I was looking for here: http://www.ozgrid.com/Excel/excel-va...ist-update.htm

    I attached an example workbook, too.

    If you type something ("Dog") in A1 (a cell with validation), a message box pops up that asks if you want to add "Dog" to the validation list. The trick is the VBA code behind the sheet. That's what I was missing. Thanks for your help!

    VR/Lost
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Re: Make a Data Validation List Grow With New Entries?

    All,

    The "growing" validation works fine if if it is on the same sheet. (See example sheet attached). You can either pick from the dropdown or add your own entry which makes the validation grow. Then you can use that grown list for validation.

    But if you go to Sheet 2 and use those dropboxes (which get their data from a Sheet 1 validation list), the list doesn't grow and causes a code error. I thought if you used a named-list, you can use that anywhere without having to specify which sheet it is. Is that he problem with the code?
    ??

    VR/Lost

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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