In my data table, the columns "Category" and "Product" are present. Product names and Category names are both common, yet the pair Category-Product only appears once in the database. There is no sorting at all in this dataset.
I have already created temporary table with unique Category names, which will be used as validation range for the first dropdown list. This part works as it should.
The second dropdown should dynamically generate a list of the products under the category you choose in the first dropdown, which is next to the first.
I can use temporary columns to produce a result that is sufficient if there are only one set of dropdowns (Category/Product).
Category: Chairs Product: Victorian
. . .
The problem is that these dropdown pairs should be part of another table with columns: 'Category', 'Product', and 'Amount'. So, when the user chooses in the first column the Category dropdown value 'Chairs', in the next column dropdown should be available in the list of just Products from the category 'Chairs'. In the next row when the user chooses the category 'Tables', the adjacent cell should be available just products from the category 'Tables'.
I am trying to make this using just formulas, array formulas, and named functions (do not mix up them with UDF functions) without VBA code.
Is it possible to do it or I am wasting my time (2 days already)?