May 15 2022 05:11 AM - edited May 15 2022 05:13 AM
Hi Excel Gurus,
I am trying to create a Dependent Dropdown List using Dynamic Arrays. When we select a client name in Column R, then column S should contain list of only related holder names. I have used dynamic array formula in each cell of range G2:G6. I tried to use SEQUENCE to achieve the same result using formula in a single cell, but the formula used in Cell G10 is returning error message.
Though I was able to create dependent dropdown list in Column S, but request yours help on the following points.
1. Kindly help to identify the issue in formula in Cell G10
2. Why can't I use dynamic array formula in Validation box directly ? I am not able to use FILTER in Validation box to create a list.
3. Can LAMBDA help to achieve the result using a single formula for Column F and G, whereby it creates a unique list of Clients in Column F and Dependent holders list in Column G ?
4. Has someone came up with something unique to tackle such situation ?
5. Can Power Query be helpful here ?
Regards
Kanwaljit
May 15 2022 06:11 AM
SolutionWhat you try to generate in G10 that's array of array which is not supported by Excel, at least directly. There are different, but similar, techniques for workarounds. As variant, in your case it could be
=LET(
clients, UNIQUE(Table1[Client Name]),
totalClients, ROWS(clients),
holderName, LAMBDA(clientName,holders,clients,
TRANSPOSE(FILTER(holders, clients=clientName)) ),
fakeArray, LAMBDA(array, LAMBDA(array)),
arrayOfFakeArrays, MAP( clients,
LAMBDA(client,
fakeArray( holderName( client, Table1[Holder Name], Table1[Client Name])))),
result, MAKEARRAY( totalClients,9,
LAMBDA(row,col,
LET(getRow, INDEX(arrayOfFakeArrays,row,1)(),
INDEX(getRow,1,col)))),
IFERROR( result, "")
)
You can't use arrays in data validation list, only ranges. More exactly references on them.
May 15 2022 06:43 AM
May 15 2022 07:38 AM
Yes, you may add it as first column making resulting array:
=LET(
clients, UNIQUE(Table1[Client Name]),
totalClients, ROWS(clients),
holderName, LAMBDA(clientName,holders,clients,
TRANSPOSE(FILTER(holders, clients=clientName)) ),
fakeArray, LAMBDA(array, LAMBDA(array)),
arrayOfFakeArrays, MAP( clients,
LAMBDA(client,
fakeArray( holderName( client, Table1[Holder Name], Table1[Client Name])))),
result, MAKEARRAY( totalClients, 10,
LAMBDA(row,col,
LET(getRow, INDEX(arrayOfFakeArrays,row,1)(),
IF( col=1, INDEX( clients, 1), INDEX( getRow, 1, col-1 ) ) ) ) ),
IFERROR( result, "")
)
May 15 2022 01:37 PM
The latest formula is working, but the First column contains only "India 01" in all the rows instead of Unique Client Names.
May 16 2022 02:45 AM
Sorry for the misprint. Corrected in attached file.
May 19 2022 05:42 AM
May 19 2022 07:26 AM
In your case everything is within LET(). You may remove added names, they are from intermediante calculations using Advanced formula environment - Microsoft Garage
May 15 2022 06:11 AM
SolutionWhat you try to generate in G10 that's array of array which is not supported by Excel, at least directly. There are different, but similar, techniques for workarounds. As variant, in your case it could be
=LET(
clients, UNIQUE(Table1[Client Name]),
totalClients, ROWS(clients),
holderName, LAMBDA(clientName,holders,clients,
TRANSPOSE(FILTER(holders, clients=clientName)) ),
fakeArray, LAMBDA(array, LAMBDA(array)),
arrayOfFakeArrays, MAP( clients,
LAMBDA(client,
fakeArray( holderName( client, Table1[Holder Name], Table1[Client Name])))),
result, MAKEARRAY( totalClients,9,
LAMBDA(row,col,
LET(getRow, INDEX(arrayOfFakeArrays,row,1)(),
INDEX(getRow,1,col)))),
IFERROR( result, "")
)
You can't use arrays in data validation list, only ranges. More exactly references on them.