Forum Discussion

KanwalNo1's avatar
KanwalNo1
Iron Contributor
May 15, 2022
Solved

Dependant Dropdown Using Dynamic Arrays

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...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 15, 2022

    KanwalNo1 

    What 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.

KanwalNo1's avatar
KanwalNo1
Iron Contributor
May 15, 2022
Dear Sergei,
Most of the things you do, seems like magic to me 🙂
Do allow me sometime to grasp the solution. You got my respect !
Thanks a Lot !

Can LAMBDA be used to produce the result in all the columns Including Unique Client List in First Column ?

Regards
Kanwaljit
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 15, 2022

KanwalNo1 

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, "")
)
  • KanwalNo1's avatar
    KanwalNo1
    Iron Contributor
    May 15, 2022

    SergeiBaklan 

    The latest formula is working, but the First column contains only "India 01" in all the rows instead of Unique Client Names.

      • KanwalNo1's avatar
        KanwalNo1
        Iron Contributor
        May 19, 2022
        Thanks a Lot Sergei !
        That is great learning source !
        You have created a lot of named ranges ! Where do you do that ?
        Directly in the "refers to" box or what ?
        I am not able to see a thing in the "refers to" box as it not re-sizeable. Am I doing something wrong ?

Resources