Issues on dynamic ranges for drowpdown lists in excel

New Contributor

Hi all


i am having issues creating a dymic range for my drop down list in excel

basically, i have started literally reworking the example provided on Excel help page 


this gives the following error message

First error.JPG


this is already weird, because all i am doing is reqorking the example, i have tabulated the list as recommended, but this is not even what i want to do, because my range has to be dynamic, if i add items to the table, these should be added in the drop down menu as choosable options. so actually what i want to do is this


second error.JPG

as you can see it actually highlights my list, so all is well, one would say.

But then i hit the ok button and the following happens

third error.JPG

this is not supposed to happen, excel recognizes the named range, there is no reason why i should get this error message. i have tried several ways, technically, as the table is only one column, i should also be able to just put =Cities in the source box, but everytime i get this error


i actually think this is a flaw in excel. But if i am doing something wrong i am hoping that someone can point out what it is that i am doing wrong here.


i hope the community is able to help me out here




5 Replies
best response confirmed by WunterSlaus (New Contributor)


You don't need sheet name in reference, just


If you add more values into the table data validation picks the up if the table and the cell(s) with data validation are in the same sheet.


Alternatively in Name Manager you may define the name (e.g. DVList) as =Table1[City] and use =DVList in data validation.

Dear Sergei

What you are suggesting is exactly what i want to do, but then, when i do it, it ends with the error message in the screen capture in my previous message.
what i want is that error message to end,



It's hard to say what's wrong without the file. Please check attached one if it works in your environment.

@Sergei Baklan 


your attached file was very helpful, i have it working now

Thank you very much