I want to use a dynamic list of names in a data validation rule in Excel and show the names in an in-cell dropdown. Is this possible?

My first failed attempt
I choose to allow "Custom" and then entered the formula =OFFSET($A$1;0;1;COUNTA($A:$A);1).
This created a correct data validation rule.
But I did not get an option to show an in cell-dropdown.

My second failed attempt
I choose to allow "List" and then entered the formula =Names. Names was a name defined as =OFFSET($A$1;0;1;COUNTA($A:$A);1).
When I choose List I have an option to show the in-cell dropdown, but the formula I entered didn't work as desired.
It only allows me to write the text string "Name" into the cell.

Do you guys have any ideas?