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. 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 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. 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 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.Įdit: Tried a roundabout, combining A3 with ListSort in a separate cell, and trying to refer to that cell for the list name. It creates a dynamic range only as long as the number of entries provided in the list. Pretty much exactly as explained in the tutorial I linked to. INDIRECT won't work with functions like OFFSET. If it's a dynamic range defined with functions like OFFSET then that's the problem. 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.

While properly combining it to AlphaListSort in the test, won't work in data validation. Plugging it into a cell results in a 'Moving or deleting cells caused an invalid cell reference error', but Meanwhile, =AlphaList and =AlphaListSort both work as they're supposed to. Data Validation works forīut not ListSort. That value dictates whether Alpha, Beta, Gamma, or Delta is called in CellA3. When the user uses the forum, they select a value from a dropdown. Great! Those work wonderfully upon testing! Until I implement them. I used the lessons I read here to essentially make a set of dropdown lists without blanks. Decided to add some quality of life features to this and looked up how to nix blanks from this list. I'm making a spreadsheet that has a lot of user-filled information and dropdowns to reference it.

Openoffice calc dynamic drop down list