+ Reply to Thread
Results 1 to 32 of 32

DATA VALIDATION - automatically add to list?

  1. #1
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Question DATA VALIDATION - automatically add to list?

    Hi,

    I'm using a DATA VALIDATION list referencing a TABLE column with this formula in the SOURCE =INDIRECT("TableName[ColumnName]") - if my required cell entry isn't in the TABLE can I somehow add it automatically?

    Thanks.

  2. #2
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Re: DATA VALIDATION - automatically add to list?

    Create a named range that refers to the values in your table column, then use that named range as the source for your validation. When you add new row to your table it should update the list automatically.

  3. #3
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Hi EchoPassenger,

    What I'm hoping to achieve is this: When the DATA VALIDATION drop down list doesn't include the value required this is typed into the DATA VALIDATION cell (Switching off the error alert) and when entered this is then automatically add to either the NAMED RANGE or even better the TABLE column?

    As a matter of learning I tried your suggesttion to update the SOURCE list to see changes in the drop down list but couldn't get that to work either.

    Cheers.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    You will need VBA to do this. Please post a small sample.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Morning John,

    As suggested I've put together the sample sheet below - hopefully communicates the task in hand? Let me know if you need more info and thanks for taking a look.

    Cheers.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Please Login or Register  to view this content.
    The above code is Sheet "Before": right-click on tab and "view code"

    The colours are a named range (Colours): if you change this you will need change VBA code

    Adding colour should extend the named range.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Re: DATA VALIDATION - automatically add to list?

    Quote Originally Posted by Cam View Post
    Hi EchoPassenger,

    What I'm hoping to achieve is this: When the DATA VALIDATION drop down list doesn't include the value required this is typed into the DATA VALIDATION cell (Switching off the error alert) and when entered this is then automatically add to either the NAMED RANGE or even better the TABLE column?

    As a matter of learning I tried your suggesttion to update the SOURCE list to see changes in the drop down list but couldn't get that to work either.

    Cheers.

    Ah ok, I understand.


    I'm by no means a VB expert (so others here may have a simpler method or find fault with mine) but technically you only need to use...

    Please Login or Register  to view this content.

    But, in the case of a long list where a value might be missed, it might be better just to check that the value does not already exist before adding it.

    So, add a button next to your validation cell and assign the following macro...
    Please Login or Register  to view this content.
    You may also want to add a few lines of code to sort your list in to order afterwards.


    Also re. my original suggestion (I should've specified that you need to be using Excel 2007 onwards) - If you manually add a value to the bottom of you table it does then appear in your dropdown list.
    Last edited by EchoPassenger; 11-25-2016 at 06:59 AM.

  8. #8
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Hi John,

    Very cool - thank you :-)

    Is it possible that code can add to the TABLE rather than the NAMED RANGE, this will allow me to keep the list in Alphabetical order?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    I am not a user of, nor familiar with TABLES, but changing "Colours" to "tablebefore" in the VBA appears to work OK.

    I changed the DV in tab "Tableafter" to reference "tablebefore" (using you INDIRECT) and it looked OK.

  10. #10
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: DATA VALIDATION - automatically add to list?

    Quote Originally Posted by Cam View Post
    Hi John,

    Very cool - thank you :-)

    Is it possible that code can add to the TABLE rather than the NAMED RANGE, this will allow me to keep the list in Alphabetical order?
    Dear Cam
    Which excel use. 2003, 2007, 2013, 2016.
    One more point : if selecting color "Blue", or "Green" or "yellow" than what result required.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  11. #11
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    @ John, thanks - I will try that now.

    @ AVK - I'm on 2016, the aim of this is to maintain a list of unique customers, in the DATA VALIDATION table the customer name may exist many times but in the reference TABLE just once - make sense?

    @ EchoPassenger - thanks - I will try your suggestions too.

  12. #12
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Hi John,

    Indeed that works well - thanks - code copied below for others who are following along.

    Now, if my DATA VALIDATION cell is actually a TABLE column, how would I re-write this line: If Target.Address <> "$E$7" Then Exit Sub .... this is my non-working attempt: If Target.Address <> Range "Table3[Data]" Then Exit Sub

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Try

    If Target.column <> x Then Exit Sub

    where x is the column number

    Column A=1, B=2 etc
    Last edited by JohnTopley; 11-25-2016 at 10:28 AM.

  14. #14
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Thanks again John - that seems to work just fine - I will play around to see if I can get this to work with a structured reference.

    Would it be OK to ask another question which have become apparent?

    The Referenence TABLE is updating nicely and set to sort Alphabetically but it seems to need an update each time a new item is added - would you know how to do this in VBA at the end the function?

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    This would require the user to trigger a macro at then end of "data entry" (using a button).

    Presumably this requires looping through the TARGET column and identifying values which are not currently in the list.

    What is wrong with current process (given my lack of knowledge of tables!) ?

    Post a sample file illustrating the need.

  16. #16
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    I've attached the working demo sheet, I have the main colour list sorted Alphabetically and then added Aplha and Fushia which appear at the end of the TABLE which as you can see don't appear alphabetically until the list is sorted again 'manually'.
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Try attached ...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    FAB - That works just great, you're a star.

    I feel bad asking another question.... last one?.... how does one de-select the reference table and return the focus to the entry cell at the end..... to cap it off nicely :-)

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Add this line before Errhandler

    Target.Offset(1, 0).Select

    ErrHandler:

  20. #20
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Thanks John,

    This is how it looks now but there is no change to the selection or cell focus?

    Please Login or Register  to view this content.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Works for me: see attached.

    Enter new colour and focus is following dropdown cell
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Hi John,

    This was of course a 'daft user' error on my part - I spliced the code into the wrong Module! The final code is copied below.

    Anyway, this is truly fantastic and your time/help is very much appreciated - hopefully of use to others too.

    Many thanks and have a great weekend.

    Cheers.

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Hello again John,

    Sorry, slightly premature in my SOLVED!.... when I ported this to my worksheet I broke it... I think because my reference TABLE [tableBefore] is actually on a different sheet in my case - how would I take care of this imortant detail in the code?

    Thanks again.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    We will have to separate the code.

    Post A TRUE example of your workbook and I'll look tomorrow.

  25. #25
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Thanks John,

    Here it is, have a good evening - cheers
    Attached Files Attached Files

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Please Login or Register  to view this content.

    This code goes in GENERAL module

    Alt+F11 to open VB EDITOR

    Click "Insert"==>"Module"

    Copy/paste code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Morning John,

    Many thanks for this, it works very nicely and is very much appreciated.

    I think the only question is how to update the code to shift the focus back to the entry cell as: Target.Offset(1, 0).Select doesn't seem to work?

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Amend code;

    Worksheets("Data Entry").Activate
    Target.Offset(1, 0).Select


    I must admit adding to DV seems to defeat the object of DV as there does not appear to be validation of the entered data!

  29. #29
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Thank you again - is it possible to deselect the "ref" TABLE at end of the function?

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Add

    Range("A1").Select

    before "End Sub" in "Update_List"

  31. #31
    Forum Contributor
    Join Date
    10-05-2005
    Posts
    112

    Re: DATA VALIDATION - automatically add to list?

    Ah yes - I was thinking about deselecting rather than selecting just one cell!

    Thanks again, I've learnt alot.

    Appreciated.

  32. #32
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: DATA VALIDATION - automatically add to list?

    Interestingly there isn't (as far as I am aware) a "deselect" option in VBA so selecting a cell appears to be the easiest way to achieve the same.

+ 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. [SOLVED] Data Validation List to automatically update
    By PDBartlett in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2014, 10:26 AM
  2. Replies: 1
    Last Post: 07-16-2013, 05:00 PM
  3. Automatically reflect List changes in data validation cells
    By cfeist in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-16-2013, 08:52 AM
  4. [SOLVED] Data validation list automatically shows the first item in the list
    By Alyena in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2013, 07:00 AM
  5. How to Automatically Add to a Data Validation List
    By ohlalayeah in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2012, 07:21 AM
  6. Replies: 4
    Last Post: 05-17-2011, 06:07 PM
  7. Replies: 1
    Last Post: 04-19-2011, 08:42 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