Dynamic Drop down list not working :-(

[Geek Girl 007]

I have created a TABLE and I'm trying to make a dynamic dropdown list from it but it keeps saying I'm using the wrong formula?
I don't know what I'm doing wrong
I did get it to work and now it won't

 

[MARK858]

Unless it's changed you never used to be able to use a Table name in data validation, try just using a normal named range (you'll have to name the range differently to the table name).

The other option is to use Indirect
in the Source box put the formula =INDIRECT("Names[Names]")
Where the first Names is the table name and the second is the header text

 

[Geek Girl 007]

Unless it's changed you never used to be able to use a Table name in data validation, try just using a normal named range (you'll have to name the range differently to the table name).

The other option is to use Indirect
in the Source box put the formula =INDIRECT("Names[Names]")
Where the first Names is the table name and the second is the header text

Hi there,
I found allot of web sites to show me how to make the list??
here is just one of them.

How to Create Dynamic Drop Down Lists Using Excel Tables - Excel TV

Updating spreadsheets can be a pain, especially when you have to change all the Data Validation dropdown menus manually. Of course, you may use a complicated mix of INDIRECT and ROW formulas to achieve a dynamic dropdown list. But there is a much better solution. And that is exactly what Zack...

excel.tv

I followed the instructions but it didn't work

 

[MARK858]

The link you provided is using a named range, which was my first suggestion.... it works fine for me.

Rather than use a formula for step 2 you can just select the range, go to the formula name box, use backspace to delete what is in the box and type your name in there and press Enter.
Then do your data validation as normal.
As I stated you must use a different Name to your table name.

 

[Geek Girl 007]

The link you provided is using a named range, which was my first suggestion.... it works fine for me.

Rather than use a formula for step 2 you can just select the range, go to the formula name box, use backspace to delete what is in the box and type your name in there and press Enter.
Then do your data validation as normal.
As I stated you must use a different Name to your table name.

View attachment 91128

Thank you soooooooo much, I missed the change of name change step.

 

[MARK858]

You're welcome