I want to create a drop-down list populated with values of Sheet2!A:A. When a specific value is selected, values in the corresponding row should be used as a criteria-range for filtering. And if at all possible, this shouldn't mess with the Undo stack.So, how do you want it to operate? You have your different filter sets in Sheet2 ... do you want to apply a particular filter at a particular time, or what?
actually you don't even need the MyCriteria name, if you are keeping that in the same sheet as the list ... just use the full range of the criteria anyway.Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.
Set up these names and formulas, and drop-down ... and use Data/Advanced Filter/In Place with Criteria of MyCriteria.
Dummy.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Country | Product | Central America | ||||
2 | Canada | Item 1 | FALSE | ||||
3 | China | Item 1 | |||||
4 | China | Item 2 | |||||
5 | China | Item 3 | |||||
6 | Denmark | Item 1 | |||||
7 | Greece | Item 1 | |||||
8 | Hungary | Item 1 | |||||
9 | India | Item 1 | |||||
10 | Japan | Item 1 | |||||
11 | Japan | Item 2 | |||||
12 | Mexico | Item 1 | |||||
13 | Norway | Item 1 | |||||
14 | Peru | Item 1 | |||||
15 | Peru | Item 2 | |||||
16 | Sweden | Item 1 | |||||
17 | Sweden | Item 2 | |||||
18 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | E2 | =ISNUMBER(MATCH($A2,INDEX(Sheet2!$B$1:$O$10,MATCH($D$1,Sheet2!$A$1:$A$5,0),),0)) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D1 | List | =Sheet2!$A$1:$A$5 |
Dummy.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Country | Product | Central America | ||||
12 | Mexico | Item 1 | |||||
18 | |||||||
19 | |||||||
Sheet1 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D1 | List | =Sheet2!$A$1:$A$5 |