+ Reply to Thread
Results 1 to 6 of 6

Dynamic Data Validation

  1. #1
    Registered User
    Join Date
    11-19-2008
    Location
    Oxford, UK
    MS-Off Ver
    2003 (Work) / 2007 (Home)
    Posts
    60

    Dynamic Data Validation

    Hi,

    I have a question on the above but can't seem to find a solution.

    There are two ways that I can find for dealing with dynamic lists via data validation:-

    1) Offset and match, cavet being the data must be sorted a-z
    2) Have lists for each potential selection

    Is there any way to get around 1 without having to do 2?

    E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?

    Thanks
    Matt
    Last edited by SystemsAccountant; 06-24-2009 at 11:50 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic Data Validation

    Matt, can you post an example - I'm struggling to visualise... you're saying something like you have your lists in B1:C3 as follows (colon represents column delimiter)

    a:s
    b:t
    a:u

    User then selects from a list containing "a,b" and assuming "a" is selected the second list should thus contain "s,u", is that correct ?

  3. #3
    Registered User
    Join Date
    11-19-2008
    Location
    Oxford, UK
    MS-Off Ver
    2003 (Work) / 2007 (Home)
    Posts
    60

    Re: Dynamic Data Validation

    Hi DonkeyOte,

    Yes that is what I am trying to do.

    Matt

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic Data Validation

    A very basic example could be...

    A2: data validation list of choices a,b
    A3: 2nd validation list - dynamic content to be based on selection in A2

    B2:C4 as previously outlined, ie:
    a:s
    b:t
    a:u

    In D2:D4 will be the final list pending selection in A2

    D2: =INDEX($C$2:$C$4,MATCH(1,INDEX(($B$2:$B$4=$A$2)*ISNA(MATCH($C$2:$C$4,$D$1:$D1,0)),0),0))
    copied down to D4

    Create a new Name

    Name: =_finallist
    RefersTo: =$D$2:INDEX($D$2:$D$4,MATCH(REPT("Z",255),$D$2:$D$4))

    Now set up Data Validation for A3

    List -> Source: =_finallist

    Is that along the right lines ?

  5. #5
    Registered User
    Join Date
    11-19-2008
    Location
    Oxford, UK
    MS-Off Ver
    2003 (Work) / 2007 (Home)
    Posts
    60

    Re: Dynamic Data Validation

    Great thanks DonkeyOte,


    So the upshot of it is that you have to create another list separate from the original one (basically a query) in order to run the second validation from?

    I had created this query list via VB (with my limited knowledge = quite cumbersome) prior to this solution. Will switch to this as it is much cleaner than what I have done.

    Kind Regards
    Matt

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic Data Validation

    Re: VBA... based on my example you could possibly use a Change event on the cell containing the Validation list such that as it changes the 2nd Validation list is purged and repopulated.

    Please Login or Register  to view this content.

+ 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