Hello,
I would greatly appreciate any help with the following problem. I've tried to come up with a solution but have come up short. Basically, I would like to come up with a dynamic data validation filter, which on an individual row, allows only valid choices depending on what has been selected in the other columns of that row.
For example, suppose you have the following database in one worksheet:
Category_Type_Size_Cost
Vegetable_Carrot_Big_1.74
Vegetable_Carrot_Small_3.87
Vegetable_Lettuce_Medium_2.01
Vegetable_Potato_Small_0.50
Vegetable_Potato_Medium_2.75
Fruit_Apple_Big_7.96
Fruit_Apple_Small_4.47
Fruit_ Apple_Medium_3.04
Fruit_Banana_Small_3.64
Fruit_Banana_Medium_4.42
Meat_Chicken_Big_0.32
Meat_Chicken_Small_2.36
Meat_Chicken_Medium_0.19
Meat_Beef_Big_2.14
Meat_Beef_Small_3.62
Then in another worksheet, you want to create a cost calculation tool which you can use to select multiple items from the database so as to calculate the sum of the costs for those items (e.g. big chicken, small carrot and medium banana). However, you also want to avoid using simple data validation lists since many of the selection combinations are not valid (e.g. there is no such thing as "medium beef"). Ideally, you also want to alphabetize and eliminate duplicates in the drop down lists. (I think I know how to do this though using a nice array formula).
The part I am having trouble with is how to dynamically restrict the lists on multiple rows. I want to replicate the same thing that excel filters do, except I want the database referenced by the filters to be on a separate sheet and, most importantly, I want to be able to have filters on multiple rows.
I hope this is clear.
Any ideas on how to do this? Or suggestions for a work around?
Thanks in advance for any advice!
Ryan
I would greatly appreciate any help with the following problem. I've tried to come up with a solution but have come up short. Basically, I would like to come up with a dynamic data validation filter, which on an individual row, allows only valid choices depending on what has been selected in the other columns of that row.
For example, suppose you have the following database in one worksheet:
Category_Type_Size_Cost
Vegetable_Carrot_Big_1.74
Vegetable_Carrot_Small_3.87
Vegetable_Lettuce_Medium_2.01
Vegetable_Potato_Small_0.50
Vegetable_Potato_Medium_2.75
Fruit_Apple_Big_7.96
Fruit_Apple_Small_4.47
Fruit_ Apple_Medium_3.04
Fruit_Banana_Small_3.64
Fruit_Banana_Medium_4.42
Meat_Chicken_Big_0.32
Meat_Chicken_Small_2.36
Meat_Chicken_Medium_0.19
Meat_Beef_Big_2.14
Meat_Beef_Small_3.62
Then in another worksheet, you want to create a cost calculation tool which you can use to select multiple items from the database so as to calculate the sum of the costs for those items (e.g. big chicken, small carrot and medium banana). However, you also want to avoid using simple data validation lists since many of the selection combinations are not valid (e.g. there is no such thing as "medium beef"). Ideally, you also want to alphabetize and eliminate duplicates in the drop down lists. (I think I know how to do this though using a nice array formula).
The part I am having trouble with is how to dynamically restrict the lists on multiple rows. I want to replicate the same thing that excel filters do, except I want the database referenced by the filters to be on a separate sheet and, most importantly, I want to be able to have filters on multiple rows.
I hope this is clear.
Any ideas on how to do this? Or suggestions for a work around?
Thanks in advance for any advice!
Ryan
Last edited:
