+ Reply to Thread
Results 1 to 7 of 7

Dynamic List in Data Validation

  1. #1
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    144

    Dynamic List in Data Validation

    Good evening,

    I have a set table with 16 rows filled with customer IDs. I am trying to create a drop down list containing only IDs but ignoring blanks. Many attempts with indirect/offset formulas have all failed miserably.

    Any idea on how to make this happen....thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Dynamic List in Data Validation

    You could pick an empty column to use for a helper.

    Enter the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which will spill down with just the non-empty cells. Then use that as your DV list.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    144

    Re: Dynamic List in Data Validation

    Thanks TMS for your response. Your solution did work if CID is static but not dynamic. CID list will sometimes compress or grow......

  4. #4
    Registered User
    Join Date
    11-18-2004
    Location
    Chennai, India
    MS-Off Ver
    Excel 2016,Office 365
    Posts
    35

    Re: Dynamic List in Data Validation

    use the formula TMS gave in Cell H2
    then in the Cell where you are using data validation, go to data validation - list - Source type =OFFSET(H2,0,0,COUNTA($H2:$H1000))
    Last edited by excel_googler; 05-21-2021 at 02:53 PM.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Dynamic List in Data Validation

    Let's say you put the formula in cell E5. That is, the helper column is column E. Use List formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in cell G5.
    Attached Files Attached Files
    Last edited by TMS; 05-21-2021 at 04:23 PM. Reason: Added updated sample file

  6. #6
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    144

    Re: Dynamic List in Data Validation

    Yep that worked

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Dynamic List in Data Validation

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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] Dynamic Data Validation List
    By zrs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2019, 03:37 PM
  2. [SOLVED] Dynamic data validation list
    By Imran/CVT in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-12-2019, 08:56 AM
  3. Dynamic data validation list
    By Imran/CVT in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-12-2019, 06:43 AM
  4. [SOLVED] Dynamic data validation list
    By martix in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2018, 01:54 PM
  5. Dynamic Data Validation List or ComboBox
    By mjo1983 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2018, 12:56 PM
  6. Dynamic Data Validation List - 2 criteria
    By smartbuyer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2018, 05:14 AM
  7. Data Validation with Dynamic List using VBA
    By nivassrii in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2011, 01:13 AM

Tags for this Thread

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