Filtered List in Data Validation

[Saighead]

Hi,

Is there a way to make Excel display filtered list based on Data Validation range? For example:

Sheet1:

Column A　　　　　　　　　　　　Column B (data validation set to List (Sheet2!$B$1:$B$100)
store1
store2
store3

Sheet2 (contains a list for Data Validation of column B on Sheet1):

Column A　　　　　　　　　　　　Column B
store1　　　　　　　　　　　　product1
store1　　　　　　　　　　　　product2
store1　　　　　　　　　　　　product3
store2　　　　　　　　　　　　product4
store3　　　　　　　　　　　　product5
store3　　　　　　　　　　　　product6

So, when I have "store1" in Sheet1:A1, I need Excel to filter the Validation List based on this value and display only "product1", "product2", and "product3" in the dropdown list for cell Sheet1:B1. Is it doable?

Also posted here

 

[Puertorekinsam]

Okay... there might be a better way to do this, but here's my idea.

Let's say you have your store validation in Sheet3 Cells A1, since you have your validation list on Sheet1 Cell A1-A3.

Add a column C to your Sheet2 with the following formula next to the products:

=if(Sheet1!A1= Sheet3!$A$1,1,0)

Then in column D on sheet 2 have this formula:

=If(D1=1,Sum(Sheet2!$D$1:D1),0)

Now in Column F have this code:

=IFERROR(INDEX(B:B,MATCH(ROW(),D:D,0)),"")

have your validation data connected to column F