dynamic and dependent data validation ranges

[AColonyOfAnts]

Rebuild Char Sheet.xlsx

Hello everyone, I'm using excel to create a homebrew tabletop RPG, and I'm running into some trouble.

I have a cell on my main sheet A19 that has the text Sorcerer_Skills (which is variable based on character class). I have a Range named Sorcerer_Skills under Name Manager with the reference "=OFFSET('Master Skill Sheet'!$H$49,,,COUNTIF('Master Skill Sheet'!$H$49:$H$56,"?*"))". I want another range of cells to have that list Sorcerer_Skills, so I use "=indirect('Main Sheet'!A19)" in the Data Validation menu with list selected.

However, I can't get the indirect function to play nicely with my offset function dynamic lists.

[Tony Valko]

I didn't look at your file.

Try something like this.

Enter the named ranges in a range of cells, for example, A1:A3.

A1 = Range1
A2 = Range2
A3 = Range3

Then, as the source for your dropdown list use:

=CHOOSE(MATCH(A19,A1:A3,0),Range1,Range2,Range3)

[AColonyOfAnts]

Thanks, it worked. So the problem was indeed that =indirect(...) interacted poorly with the =offset(...).

[Tony Valko]

Thanks, it worked.

Good deal! Thanks for the feedback!

So the problem was indeed that =indirect(...) interacted poorly with the =offset(...).

Yes