0

I've been trying to find a way of doing this with dependent dropdowns, but I am yet to be successful with my specific tables. In essence I'm looking to create a validation list based on a "filtered" results list from another table.

These are structured tables:

Table1 (with static values to be "filtered" )

id      Items
2       ItemAA
2       ItemAAA
3       ItemZZ
5       ItemXY
5       ItemXYZ
2       ItemCC

Table2 (With the Validation List dropdown)

idFromTable1  RelatedOptionFromFilteredDropdownList
2             ItemAA  (dropdown lists: ItemAA, ItemAAA, ItemCC)
5             ItemXY  (dropdown lists: ItemXY, ItemXYZ)
2             ItemCC  (dropdown lists: ItemAA, ItemAAA, ItemCC)
3             ItemZZ  (dropdown lists: ItemZZ)

Edit: using formulas, not VBA

Edit2: Both ID and Items will be counted on the hundreds or thousands.

Thank you

1
  • The thing is that I don't know where to start with this, specifically with how to create that "filtered list" within the validation list formula.It's one thing to return a single text value, there's a lot of options for that, but I haven't found how to return a vector with the multiple filtered values.
    – acseven
    Apr 20, 2017 at 16:29

1 Answer 1

0

Create a named list

  1. First, sort your Table1 by ID
  2. Create a named range with the name listlookup (or whatever).
  3. Put this formula on the listlookup

=OFFSET(INDIRECT(CELL("address", INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)))), 0, 0, COUNTIF($A$2:$A$5, $C1), 1)

 ID | Items   | lookup_value_here | dropdown_list_here
 1  | ItemXY
 1  | ItemXYZ
 2  | ItemZ
 2  | ItemZZ
  1. Create the drop down list, and refer the list to listlookup

Explanation

INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)) finds the first cell that has the lookup_value

CELL( ... ) returns the string referring to the cell (for example: $B$2 for lookup_value 1)

INDIRECT( ... ) converts the string reference to cell reference

OFFSET( ... , 0, 0, ... , 1) returns the range from the lookup (for example: B2:B3 for lookup_value 1)

COUNTIF( ... ) returns the number of data matching the lookup_value


This is fun, because I've stumbled into this problem in the past and can't solve it.

5
  • I'm sorry, but this is the method how you do any dependent validation list, as far as I can see. In any case, it's impossible because there can be thousands of options in the ID field...
    – acseven
    Apr 21, 2017 at 7:42
  • Updated answer. I hope this helps
    – Vylix
    Apr 21, 2017 at 8:51
  • Thanks for the followup. Although this is more comprehensive, I can't seem to use it as a validation List, only on a cell while issuing CSE, which returns just the first value. I guess the validation list would require an array result. I found another question where I think the issue was the same as mine but in the end I can't use the named range in the validation list, it returns a blank list. stackoverflow.com/questions/26803196/…
    – acseven
    Apr 21, 2017 at 13:37
  • The OFFSET returns array. The formula is intended to be used in name manager, and called in the data validation, not entered in the worksheet as array formula. Can you elaborate more, or give a screenshot of your result?
    – Vylix
    Apr 21, 2017 at 13:54
  • Ok, I've reviewed what I was doing and got it working like your example. And though it is a significant step, I still have to check if I can make your dropdown_list_here field run for every line of a given table. But the one big issue I have right now is that there is no way I can have the table sorted as you mention right on the first step.
    – acseven
    May 4, 2017 at 19:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .