Data Validation Error Popup

Question

Hi was trying to create a dynamic dropdown in data validation lists through a name table

1. created table gave name tblC
2. selected to data validation and at source gave =tblC and then I get a popup "There is a problem with this formula"
3. tried the =tblC in another cell and it references the table

Even tried Offset formula and it gives the same error ... and if I use the formula on another cell it displays the content.

The only way this has worked is if select the source region making it static

Can you please help ... what am I doing wrong here

Answer 1

Hi VinojJM,

It seems that didn’t give a define name for your “Name” column in your Excel, which the Data validation won’t recognize the data in your table in this scenario.

Here is a demo I created. The table I created was named as tbIC. Go to Formula tab > Define name > define a name to your  column in the table. Here I defined “Source name list” column as “Name“ in my table.

 ![Image](https://learn-attachment.microsoft.com/api/attachments/a5fa6731-ca02-47a1-bfc1-4f6941cb562a?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/eb764949-fb0c-4fb4-bb53-bc35e262c53c?platform=QnA" rel="ugc nofollow">![Image](https://learn-attachment.microsoft.com/api/attachments/eb764949-fb0c-4fb4-bb53-bc35e262c53c?platform=QnA

Answer 2

Hi VinojJM, 

Have you tried the steps above to  create your Dynamic Data validation drop down based on your table, if there are any updates, please feel free to let us know below.

Best regards,

Dihao