INDIRECT(CONCATENATE( and Blank-Free dropdowns

[WitchRolina]

So here's a strange one.

I'm making a spreadsheet that has a lot of user-filled information and dropdowns to reference it. Decided to add some quality of life features to this and looked up how to nix blanks from this list. And it totally works.

Sort of.

I've got it set up like so:

There are four named lists:

AlphaList
BetaList
GammaList
DeltaList

I used the lessons I read here to essentially make a set of dropdown lists without blanks. They are:

AlphaListSort
BetaListSort
GammaListSort
DeltaListSort

Great! Those work wonderfully upon testing! Until I implement them.

When the user uses the forum, they select a value from a dropdown. That value dictates whether Alpha, Beta, Gamma, or Delta is called in CellA3. So I use

=INDIRECT(CONCATENATE(A3,"ListSort"))

...and it goes to hell. Data Validation works for

=INDIRECT(CONCATENATE(A3,"List"))

but not ListSort. Meanwhile, =AlphaList and =AlphaListSort both work as they're supposed to. Plugging it into a cell results in a "Moving or deleting cells caused an invalid cell reference error", but

=CONCATENATE(A3,"ListSort")

while properly combining it to AlphaListSort in the test, won't work in data validation.


It should be noted that due to the shared, user-input nature of this spreadsheet, that backwards and sideways compatibility is important - with a target goal of this being fully compatible with google docs.