How to create dynamic Validity list using formula?

[mikekaganski]

I need to create validity lists (not only to limit input, but also to provide drop-down choice), based on a formula, which would lookup a value from the same row, in a range elsewhere. I can’t easily find answers; most answers that I find tell about formulas to detect validity, but not provide multi-choice list.

ValidityWanted.ods (9.6 KB)

In the attached document, there are two sheets: Sheet1 and Sheet2. Sheet2 has the range to look up in. Sheet1 is where I need to place the validated cells (B1:B3); in B1, I need a formula that would lookup value from A1 (“a”) in Sheet2.A1:B9; and the resulting list needs to be “1, 2, 3”.

Is it doable? Thank you!

[karolus]

Hallo

»criteria« and »values« are the so-named-ranges in Sheet2

ValidityWanted_solved.ods (12.4 KB)

[mikekaganski]

Great! It’s a pity that it adds empty entry to the list; unfortunately, it might be in the middle, if the looked up value is not contiguous in the range. But it’s likely good enough (I will need to check).
Thank you!

[karolus]

with Option Sort Entries ascending the empty Entries goes to the bottom!

[mikekaganski]

I definitely need to take a sleep. I don’t see the things shown to me on your nice screenshot … what can I say! Thank you!

[Timur]

Very elegant. I spent some time figuring out why it did not work in my real file, until I applied TRIM to both criteria and A1, and it was OK with that
I mention this file and dropdown in other ticket Calc: Print with all dropdown list items.