# Dynamic Data Validation

1. ## 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

2. ## 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. ## Re: Dynamic Data Validation

Hi DonkeyOte,

Yes that is what I am trying to do.

Matt

4. ## 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. ## 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. ## 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.``

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

#### 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