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...
='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
