I have a data table of product attributes with the columns brand, subcategory and product. I have drop-list in a column of another sheet and next to that a subcategory drop-list. The method I usually use to make these dynamic is to use and OFFSET function. My problem is that the subcategory can appear in the dataset multiple times as there can be multiple products that have the same brand and subcategory; when the array is returned to the drop-list by the OFFSET function it therefore has multiples of the same subcategory. I could use the UNIQUE formula to create a list of distinct subcategories, but the filter changes on each row of my input sheet so that doesn't work. I could also create multiple subcategory lists and use the unique formula on each, but if a new subcategory is added that necessitates a new range be created.
So, my question is: how do I create a dynamic range with only unique values from within an OFFSET on each row of my input table. Essentially I'd like to filter the list as an array with each of the subcategories associated with a brand listed once only. But the kicker is I need to be able to do this on multiple rows with different brands entered. I hope that makes sense.
So, my question is: how do I create a dynamic range with only unique values from within an OFFSET on each row of my input table. Essentially I'd like to filter the list as an array with each of the subcategories associated with a brand listed once only. But the kicker is I need to be able to do this on multiple rows with different brands entered. I hope that makes sense.
