Dynamic Validation List from DB Like Table

[codewalk3r]

Hi,

Trying to get a dynamic validation list (see field 2 below) without using INDIRECT function due to the large volume of the data (which would be impractical) which is structured similarly like below:

Column A | Column B
Place A | User A
Place A | User B
Place A | User C
Place B | User B
Place B | User D
Place C | User A

Field 1 : Validation list of unique items from column A (solved)
Field 2 : Validation list of all users located from the value selected from field 1.

So far, all my research always points to using the INDIRECT function.

[bhenlee]

kamusta codewalk3r?

You can try this formula in your 2nd validation:

=OFFSET(LIST2,MATCH($D$1,OFFSET(LIST2,0,-1,COUNTA(B:B)),0)-1,0,COUNTIF(OFFSET(LIST2,0,-1,COUNTA(B:B),1),D1),1)

I used named ranges on your 2nd list and this formula assumes your entire lis on the same sheet. if not you can always revert to using named ranges.

[codewalk3r]

Salamat!

Upon testing it, the list has numerous duplicates. Can the dups be removed when the list is generated?

[bhenlee]

Of course. That depends on how your validation list is setup.