Is there any way to create a dynamic, dual-dependent dropdown list? See attached example file. It has a list of grocery products, each having a Department, Category, and Item (such as Dairy, Yogurt, and Dannon blueberry yogurt 4 oz. 4-pack). I'd like to create a dropdown list that shows the list of Items associated with a chosen Department and Category, where the dropdown list reflects any changes in the base list.
I tried setting up some simple array formulas to extract the selected Items (cells K8 and K12). The first version [using IF(AND] doesn't work (I'm not sure why?). But no matter; I added a helper column "Combo" that combines the Department and Category, and my second formula (cell K12) works, in that it does find the matching Items. But it also (of course) has a FALSE wherever a match does not occur.
So Question 1: Is there any way to get rid of the FALSE entries and return (via the array formula) only the matching Items?
I then tried to set up data validation (list) in cell K4. I discovered that you apparently can't enter an array formula (copying the formula in K12) into a list validation source. So I tried simply referencing cell K12. That gives me a single-entry list (only the first entry).
So Question 2: If I could create an array formula to extract only the matching Items, is there any way to (a) use that formula directly for data validation, or (b) reference that entire list for data validation?
Bookmarks