I am making dependent lists using the UNIQUE() function and I'm running into a problem... Here is the context:

I have a worksheet called "Code" containing all the data (hence, all the categories and the corresponding products) and a worksheet called "Result" that contains the final dropdown list.

Here is what I have in the code worksheet

Here is what I have in the Result worksheet

In the code worksheet, in cell A2, I wrote this function: =SORT(UNIQUE(Result!H3:L3;TRUE;TRUE)) The sort function is to have my result in alphab. order and unique since i may extend my list over the years, so I purposely left blank cells but I don't want them to appear in my dropdown list.

In the same sheet, in cell A5, I wrote this function: =SORT(UNIQUE(XLOOKUP(Result!C4;Result!H3:L3;Result!H4:L8);;TRUE))

Now going on the result sheet, in cell C4, I went to the data tab, data validation and I chose list and input this:

And following the same logic, in cell D4, I have this:

Now for the column of category, I simply copied the list and input it in the rows bellow it until row C12 and I still get the 3 choices : gaming, productivity and school. However, I get a problem when it comes to the products column. When I copy the list from cell D4 and put it in cell D5, the list is still linked to the cell C4. However, I need it to be linked to cell C5. In that same logic, I need cell D6 to be linked to cell C6, D7 to C7, etc.

Any help is very appreciated! Thank you!

CodePudding user response：

The formula is using CELL("row") which returns the row of the current selection. BUT only after a calculate - therefore we need the selection_change-event

=LET(d,SORT(UNIQUE(FILTER(configProductCodes,
    (configCategories=INDEX(userCategory,CELL("row")-1,1)) * (configCategories<>""),""),TRUE,TRUE)),
FILTER(d,d<>0))

I am using named ranges - as you can see in the screenshot.

The formula is placed in E9 which is called dropdownValues.

You need this code in the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("dropdownValues").Calculate
End Sub

Any time you change the selection formula gets recalculated based on the current row.

Use this cell for your validation list.