INDIRECT(CONCATENATE( and Blank-Free dropdowns

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.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Not that this will fix anything, but you dont need to use CONCAT
=INDIRECT(A3&"ListSort")

So, Tell me (exactly) what would be in A3, and if you just use =A3&"ListSort" what do you get?

Originally Posted by WitchRolina

And it totally works.

Sort of.

In other words, it doesn't work!

How is AlphaListSort defined?

If it's a dynamic range defined with functions like OFFSET then that's the problem.

INDIRECT won't work with functions like OFFSET.

Originally Posted by Tony Valko

How is AlphaListSort defined?

If it's a dynamic range defined with functions like OFFSET then that's the problem.

INDIRECT won't work with functions like OFFSET.

Pretty much exactly as explained in the tutorial I linked to. It creates a dynamic range only as long as the number of entries provided in the list. If indirect doesn't work with dynamic ranges, is there a way to call a named dynamic range in the way I mentioned in the first post without it that will work?

@FDibbons: Changing it to =A3&"ListSort" produces error message "The list source must be a delimited list, or a reference to a single row or column." The error message does not occur when it does not try to combine the cell contents with the string to complete the list name, it just works as it should.

Edit: Tried a roundabout, combining A3 with ListSort in a separate cell, and trying to refer to that cell for the list name. I get the same problem, so it's not the act of combining it in-cell that's the issue, it's the fact that I need to refer to the named list, and not straight up name it. Again, =AlphaListSort works fine in data validation, but trying to refer to it from cell data does not, which seems completely arbitrary to me.

I should also mention that there are compatibility concerns to keep in mind - solutions should be compatible with google docs due to the shared nature of this particular spreadsheet.

Attach your file here, as I described in post #2.

Originally Posted by WitchRolina

solutions should be compatible with google docs due to the shared nature of this particular spreadsheet.

I have no experience with Google docs so I don't know what would be compatible and what wouldn't.

When I asked you to post the file, I was trying to help. I would have taken a look at it and, who knows, I or someone else might have been able to sort this out for you by now. If you don't want to attach the file for some reason, that's fine, but I would not have expected to have received negative rep for attempting to help you: we learn something new every day!

I was actively trying to help. I help a lot of people here, as you can see from my reputation points - helping others is what motivates me to be here.