Forum Discussion
Excel Table Appears to Automatically Expand but drop down list doesn't update
I used Excel 2013. I created a drop-down list that is based on an Excel Table via the Data Validation button on the Data ribbon. If I add or delete a row from the middle of the table, my associated...
May I know what is the value that you put in the data validation?
You cannot use table value directly in data validation
=MyTable[Column1]
If you say you are using table in data validation, I guess you are using formula to select a range, e.g.
=Sheet1!$A$1:$A$100
If you really do it as above, when you insert a row in middle, the new row is still in the range, and the formula will automatically update as
=Sheet1!$A$1:$A$101
When you insert a row at the bottom, the table is expanded, however, the formula in data validation will not.
When you insert a row at the top, the table includes the new row, but the formula in data validation will be updated as
=Sheet1!$A$2:$A$101
Therefore, the data in the new rows is not included in the list.
You can add a Name, MyList, in your workbook
=MyTable[Column1]
Then, use this Name in data validation
=MyList
='Device Type'!$A$2:$A$76
I created a list on a separate tab in my workbook called Devices. My data list is called DeviceType and has 77 rows including a header. I did a Ctrl + T to turn it into a table. I then went and highlighted the column on my original worksheet went to Data >Data Validation and chose my table as the source. I really wish there was one straight forward tutorial on how to create a list that updates dynamically(all rows) in Excel using Data Validation.
Hi Rhonda, the detailed steps as below:
- select cells 'Device Type'!$A$1:$A$76 (you have done)
- press Ctrl + T to turn it to table, and then check the box "My table has headers" (you have done)
- select a cell of the table
- select "Table Tools" tab in the Ribbon
- change the table name to whatever you want (I make it as MyTable)
- select "Formulas" tab in the Ribbon
- click "Define Name"
- give a name (I make it as MyList)
- keep the scope as workbook
- type the following formula in "Refers to"
=MyTable[Column1]
where "Column1" is the header, i.e. the content in your workbook 'Device Type'!$A$1 - select the cell that you want to use Data Validation
- select "Data" tab in the Ribbon
- click "Data Validation"
- in the "Settings" tab, select "List" for the dropdown box of "Allow"
- in the textbox of "Source", type the following
=MyList
- Try to insert items in your table, and test if it is reflected in the dropdown list
I am sorry about the previous reply. I was trying to explain more about what Excel is "treating" the insertion that you made. Again, sorry. Please try the steps above to see if it is what you expected.
- Because I was limited to saving to an .xls file type, this was the only solution that would work for me. Thank you
