Data validation drop-down list based on criteria in the row?

Question

I want a data validation drop-down list that populates based on the value in another column in the same row. I've found a lot of great examples with multiple dependent drop-down lists, but unfortunately I don't think those will work in this case. Instead of a dynamic list based on a single fixed cell, I want a whole column of these, each based on its own row and independent of the others.

Here's the setup:

SPELLS KNOWN

"Spells Known" is the data source, containing Level and Name columns. A given Name can appear multiple times, corresponding to different Levels. (It's also important for each Level to have its own unique list, because the table contains additional columns with level-specific values, e.g. a Level 3 Heal has different values than a Level 1 Heal.)

Level	Name
1	Heal
1	Magic Missile
1	Sanctuary
2	Dispel Magic
2	Heal
2	Resist Energy
3	Heal
3	Magic Missile

SPELLS PREPARED

"Spells Prepared" is the user-facing table. The idea is to specify the Level and then use a drop-down in the Name column to select from the values listed for that Level.

Level	Name (select from drop-down)
1	{Heal, Magic Missile, Sanctuary}
2	{Dispel Magic, Heal, Resist Energy}
3	{Heal, Magic Missile}

And here's what I've tried:

Since it's not possible to get a drop-down when using "Custom formula is" in Data Validation, I tried to combine the values into a comma-delimited list in a single "helper" cell on the same row as the drop-down, using the following:

=ARRAYFORMULA(TEXTJOIN(",",TRUE,IF(SpellsPrepared_Level=SpellsKnown_Level,SpellsKnown_Name,"")))

The problem is that data validation treats this output as a single string—that is, the drop-down has only one option: "Heal,Magic Missile,Sanctuary", instead of recognizing the comma delimiter like it would if you had entered the same string as a list directly into the Data Validation window, and thereby treating the list as three items.

It seems like the next thing to try would be to create a new sheet where Column A equals SpellsPrepared_Level, and Columns B, C, and so on each contain a single SpellsKnown_Name value in the list based on that Level. Then, data validation could use a "List from range" and build the drop-down list from those values for each row.

But that doesn't seem terribly elegant. Is there a more efficient way to accomplish this? Let me know if having the actual sheet would be more helpful.

Answer 1

The next-to-last paragraph is almost exactly what I ended up doing.

1. Add a helper sheet for Spells_1 named Spells_1_Helper
2. Fill Column A with the formula =Spells_1!F:F where F is the column containing Level.
3. Fill Column B with the following formula:

=IFERROR(IF(ISBLANK(Spells_1!F:F),"━━ Enter a Level and then open this menu again. ━━", IF(NOT(OR(AND(ISNUMBER(Spells_1!F:F),Spells_1!F:F>=1,Spells_1!F:F<=10), Spells_1="C",Spells_1!F:F="R")),"━━ Invalid Spell Level ━━", IF(FILTER(Spells_1_Name,A1=Spells_1_Level)="","━━ No results for this level. ━━", TRANSPOSE(FILTER(Spells_1_Name,A1=Spells_1_Level))))),"━━ No results for this level. ━━")

4. The TRANSPOSE(FILTER) statement gives a list of the values (spells) that correspond to the input level. It's transposed to go across the row instead of down the column so that each row has its own level input and list. The first four lines filter out blanks and invalid inputs and add some user-friendly help text that will appear in the drop-down in case of an error.
5. Back on the Spells_1 sheet, use Data Validation to create a drop-down for the first row (29, in this case). Select List from range, and enter the range =Spells_1_Helper!$B29:29.
6. Copy the drop-down cell down the column as needed. When you enter a value in the Level column, the menu pulls in the corresponding list from the helper sheet.

Bonus: Add IF(ISNUMBER(SEARCH("━━",$O29)),"",[...] to formulas that pull data related to the result you select from the drop-down, so that you can include "━━ help text ━━" or "━━ headers ━━" in the list of results without causing ugly errors if someone selects them.