0

I need to do a rather complex cascading filtering with multiple dropdown cells.

Ex:

A  1

A  2

A  3

B  1

B  2

B  3

There are still another colunm A 1 has five more options. I would appreciate any help. Even if just a general direction. I have seen tutorials for only for non-repetitive cells like above. I would have:

A  1  z

A  1  y

A  1  w

and so forth.

1
  • What do you mean with non-repetitive cells? Can you edit your example and make it a bit clearer what you want to achieve? If user selects A in first dropdown, then they should see 1,2,3 as options in second dropdown. Is that correct? Where does z,y,w feature? is that a third dropdown?
    – teylyn
    Aug 13, 2016 at 23:19

2 Answers 2

1

Looks like what you are after is dependent data validation. This can be achieved by arranging the data for the different drop-downs in named ranges and using Indirect to call the dependent range. Examples and full tutorial can be found at Deb Dalgleish's www.Contextures.com site here

0

So after two days... I found this tutorial . It brings the repetitive items. That is 50%. Basically, there are two lists.

a)a list with no repetitive terms. In the case above.

    A
    B

b) Then you have a list with all repetitive cells:

A
A
A
B
B
etc.

Need to create the Index() and offset () as described in the tutorial above;

c) Then you have your dropdown cells reflecting "a", NOT 'b':

d) I used =CONCATENATE(cell, cell, etc) Now you will have something like this A1street (3 columns, "A:, "1", "street").

Use this to create a table with concatenated terms. Add your last column (the result needed after choosing 2, 3 etc dropdowns) to the right of the concatenated values. In my case, ex,

A1street   $10,45

d) finally you use the lookup function to match the concatenated terms (as you use the dropdowns) with a specific cell showing the concatenated cells and compare with your new table.

You must log in to answer this question.

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