In cell O2 below, I want to propose a drop down list box of values which are defined in the column Group of the table Table2 (this table is located in the columns L and M), but I get a generic error message "There's a problem with this formula" when I enter =Table2[Group]. I know this syntax works when I use it directly inside a cell (for instance =MATCH("Feature",Table2[Group],0) returns 2).

(you can see at the end of the question a screenshot of what I want to obtain)

Excel Data Validation

The syntax seems to be partially accepted by Excel, because as you can see in the screenshot above, it's able to locate correctly the values in the column Group, because they appear in a dotted-green box.

For information, below is the name of the table, defined as a Table, and see that it appears in the Name manager:

Excel Table name and Name manager

My question: do you know if there's a solution to get the list of values by referring to the Table Name and Column?

The final result I expect (that I can successfully get with Source like Bug,Feature,Not urgent,Quick bug,Removed,To remove,UX bug, or via a Name created manually like =myname, but not with Table/Column names):

Excel cell possible values via Drop Down List Box

NB: my Excel version is Excel 365 version 16.0


Englobe your table with INDIRECT function. Your source will be:


enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.