Error when creating a dynamic dropdown with reference to table

[san92]

Hello everyone, I'm new to the forum and unfortunately could not find a solution to my problem by means of the search function and hope that I do not ask anything here now that has already been answered.


To get straight to the point:
When creating a dynamic drop-down list via the data validation mask, I get an error message when I specify a reference to a table as the source.

I have attached a screenshot as an example:
In the worksheet "Table 1" I have the table "Name", which I want to use as source for the drop-down menu. I have renamed this table to "Name". In the worksheet "Table 2" is the table for which I want to use the drop-down menu (in column "Name selection").
In the next screenshot I have called the data validation and entered "=Name" as source.
Subsequently, I always get the same error message (last screenshot).
I have now also already changed the table name, but then get the message that the name already exists. When checking via the name manager, however, nothing is listed.... It's all a bit confusing for me and I would appreciate some help.
Does anyone have a tip for me, or can possibly tell me what to google or search here in the forum to find the solution, if it is already a frequently asked problem?


Thank you so much

 

[Rows N Columns]

Did you create "Name" as a named range or as an Excel Table? If it is a Table, in the data validation, try entering

=INDIRECT("Name[Name]")

The first "Name" in the formula is the name of your table, and the second "Name" is the name of the column.

So if your Table is called "Name", and your column header is "Name Selection", formula would be

=INDIRECT("Name[Name Selection]")