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.
Category Product
======== =======
Chairs Victorian
Chairs Beautiful
Chairs Edwardian
Chairs Gross
Tables Victorian
Tables Edwardian
Tables Huge
Tables Kool
Tables Lambda
Closets Edwardian
Closets Excellent
Closets Major
Closets Hello
Chairs Huge
Tables Picturesque
Closets Picturesque
Chairs Incredible
Closets Minor
Chairs Just
Chairs Kool
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.
Categories
==========
Chairs
Tables
Closets
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
Beautiful
Edwardian
Gross
Huge
. . .
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)?
