Attempting to create adaptive data validation list

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:

	A	B	C
1	Cherry	Apple	Bananas
2	Amarena	Gala	Cavendish
3		Fuji	Blue Java
4		Granny Smith

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.

	A	B
1	Category	Items
2	Apple	(dynamic dropdown)

Here's a link to what this 2nd list is trying to pull from. Column A is constructed by transposing the column headers, and is the 1st list.

my 2nd list should dynamically search the table and show the drop-down, but the titles of each column will be changing over time.

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.

Solution

Here's an approach without the need for (transformed) duplication of the data elsewhere in the workbook:

=OFFSET(
    INDEX(Lists!$A$1:$E$1, 1, MATCH($A2, Lists!$A$1:$E$1, 0)),
    1, 0,
    COUNTIF(
        OFFSET(
            INDEX(Lists!$A$1:$E$1, 1, MATCH($A2, Lists!$A$1:$E$1, 0)),
            1, 0, 5
        ),
        "<>" & ""
    )
)

Assuming (as an example) the following in Sheet Lists:

I.e.:

Expecting a maximum of 5 columns (in the formula adjust $E according to needs)
Expecting a maximum of 5 rows (in the formula adjust 5 (at end of OFFSET) according to needs)

For your categories in 'Item Info'!A2:A*, I've used:

=TRANSPOSE(FILTER(Lists!$A$1:$E$1,Lists!$A$1:$E$1<>""))

(Again: adjust $E according to needs.)

Setup

In Sheet Item Info select the entire appropriate range in Column B for actual and potential matches in column A. In my example, this would be B2:B6 (recall: we expect a maximum of 5 columns).
Go to Data -> Data Tools -> Data Validation (shortcut: Alt + A + V + V).
Select List and in the Source field enter above formula.

Here is the formula without the indentation:

=OFFSET(INDEX(Lists!$A$1:$E$1,1,MATCH($A2,Lists!$A$1:$E$1,0)),1,0,COUNTIF(OFFSET(INDEX(Lists!$A$1:$E$1,1,MATCH($A2,Lists!$A$1:$E$1,0)),1,0,10),"<>"&""))

In action:

Explanation Formula

We use MATCH to locate 'Item Info'!$A2 in the header (relative row reference to accommodate drag down).
We use the result of MATCH inside INDEX to get a Reference to use inside OFFSET. Note that we do this to avoid INDIRECT and ADDRESS. Both are so-called volatile functions, which means that they are continously recalculated every time there is a change in the workbook. That would needlessly impact performance.
For OFFSET, we now have the Reference. We add:
- Rows offset: 1 (we start one row below header)
- Height. We could just set this to the maximum amount of rows (i.e. 5 in our example), but that would leave a trailing blank for the dropdown. So, instead we apply COUNTIF on that maximum range and count only the cells that are non-BLANK. N.B. This means that all your entries need to be entered in consecutive rows. I.e., don't leave blanks (you could apply conditional formatting to alert users to such an error when they add entries). If you don't want to risk it, just get rid of the COUNTIF and settle for the trailing blank in the dropdown.

Prettified version with LET:

=LET(
    HEADER, Lists!$A$1:$E$1,
    IDX_CAT, MATCH($A2, HEADER, 0),
    START, INDEX(HEADER, 1, IDX_CAT),
    OFF, OFFSET(START, 1, 0, 5),
    NON_BLANKS, COUNTIF(OFF, "<>" & ""),
    OFFSET(START, 1, 0, NON_BLANKS)
)