Data validation dropdown with in cell-dropdown?

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?

Sure, here it is. You can see that I have made two attempts and that each has a disadvatange. Book1.xlsx

I want data validation with a dynamic list that has an in-cell drop-down