Excel Lambda function to simplify the creation of multiple dependent dropdowns. Valid on Web and Windows Desktop [v. 2405 (Build 17602.20000) Beta Channel].

MultipleDependentDropdownsWithoutVBA.txt

/*
Name: Excel Dependent Dropdown
License: MIT
Description: Excel Lambda function to simplify the creation of multiple dependent dropdowns. Valid on Web and Windows Desktop [v. 2405 (Build 17602.20000) Beta Channel].
Author: https://gist.github.com/Iremlap
Citation: Based off the approach shared by Leila Gharani in this YouTube video: https://www.youtube.com/watch?v=7mo4COng7Sg.
*/

/* Dependent Dropdown

	Arguments
		- PrimaryDropdownSelection (Reference) = A reference to the first cell, in a column or row of cells, which contains the primary dropdown selection (Data Validation List) - using a static column value. Ex: $A1
		- PrimaryDropdownOptionsLocationAsText (Text) = Text consisting of the address of the cells containing the options used for the the primary dropdowns, listed below which are the secondary dropdown's options concluded with a blank row - using static row and column values. Ex: "$H$29:$C$29"  - OR -  "Sheet2!$A$1:$C$1"  - OR -  "DataValidation!$A$1:$C$1"
		
*/

DROPDOWN.DEPENDENT = LAMBDA(PrimaryDropdownSelection, PrimaryDropdownOptionsLocationAsText,
	LET(
    PrimaryDropdownOptionsReference, INDIRECT(PrimaryDropdownOptionsLocationAsText),
    OptionsLocationisForeignSheet, IF(
        ISNUMBER(SEARCH(CHAR(33), PrimaryDropdownOptionsLocationAsText)),
        
    ),
    AddressOf_i1_PrimaryDropdownOptions, INDIRECT(
        IF(
            OptionsLocationisForeignSheet,
            TEXTAFTER(TEXTBEFORE(PrimaryDropdownOptionsLocationAsText, ":"), CHAR(33)),
            TEXTBEFORE(PrimaryDropdownOptionsLocationAsText, ":")
        )
    ),
    DataValidationMaxOptionsCount, 32767,
    _GetSecondaryDropdownOptions, LAMBDA(Qty,
        OFFSET(
            AddressOf_i1_PrimaryDropdownOptions,
            1,
            MATCH(PrimaryDropdownSelection, PrimaryDropdownOptionsReference, 0) - 1,
            Qty,
            1
        )
    ),
    AllSecondaryDropdownOptionSlots, _GetSecondaryDropdownOptions(DataValidationMaxOptionsCount),
    ContiguouslyFilledOptionSlotCount, XMATCH(, AllSecondaryDropdownOptionSlots),
    DependentDropdownOptions, _GetSecondaryDropdownOptions(ContiguouslyFilledOptionSlotCount),
    DependentDropdownOptions
	)
)