I am attempting to create a dependent drop-down list for an inventory, that will be adaptive as categories & lists change in the foreseeable future.

VBA is blocked, so my solution has to be done without any VBA tools. Version: Excel 365 v2404 (used company-wide).

Sheet Lists contains the lists like this:

Note that lists may be added in the future (in column D, E, etc.), and each list of items may grow or shrink.

Sheet Item Info has a dropdown repeating through column A with the list names. The goal is that when the user changes this value, the data validation list in the adjacent cell (B2 for example) will be updated to reflect the correct list items. Below I’ve selected “Apple”, so the cell next to it should now have the options: Gala, Fuji, Granny Smith.

Attempts

My latest attempt has been to input the following formula into the “Data Validation List” formula field, which gave me an error and did not function (despite operating just fine within the sheet when tested).

=SORT(
    UNIQUE(
        FILTER(
            INDEX(Lists!$A$1:$Z$50,0,MATCH('Item Info'!$A2, Lists!$1:$1, 0)
            ),
            (INDEX(Lists!$A$1:$Z$50, 0, MATCH('Item Info'!$A2, Lists!$1:$1, 0)) <> "") *
            (INDEX(Lists!$A$1:$Z$50, 0, MATCH('Item Info'!$A2, Lists!$1:$1, 0)) <> 'Item Info'!$A2)
        )
    )
)

My earlier attempt consisted of a formula that was too long for inserting into the “Data Validation List” formula field. I tried splitting it up into 2 parts, and then reference the cells in the formula field, but without success:

Cell1 (F2):

=ADDRESS(
    2,
    MATCH(
        'Item Info'!$A2,
        INDIRECT("'Lists'!$A$1:" & ADDRESS(1, COUNTA(Lists!$1:$1))),
        0
    ),
    ,,
    "Lists"
)

Cell2 (G2):

=ADDRESS(
    COUNTA(INDEX(Lists!$A:$Z, 0, MATCH('Item Info'!$A2, INDEX(Lists!$A:$Z, 1, 0), 0))),
    MATCH('Item Info'!$A2, INDIRECT("'Lists'!$A$1:" & ADDRESS(1, COUNTA(Lists!$1:$1))), 0),
    ,,
    "Lists"
)

These two formulas also got me the correct range, yet my attempt to reference them in the “Data Validation List” formula field lead to an error:

=INDIRECT(F2):INDIRECT(G2)

In reply, Excel told me:

You may not use reference operators (such as unions, intersections, and ranges), array constants, or the lambda function for data validation criteria)

However, combined the formula I attempted is 306 characters, so that it would exceed the limit (255). Any guidance on how to solve my issue would be greatly appreciated.