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 drop-downs are updated automatically. However, if I insert a row of data at the very top or bottom of the list (range), even though the table appears to have expanded, the drop-down list does not update automatically. The Auto Correct options "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns" are checked. None of the sheets on my workbook are protected.
I finally figured this out. The result is when you add an item to a table, even at the bottom, the drop down list automatically expands to show all values in your table of drop down items.
Basically, create a named range to be equal to your defined table[column_header] which contains your list. I'll use a list of fruits as an example.
- Type in a header, (i.e. "name")
- Type in some names of fruits below that header, one per row (i.e. Apples, Bananas, Oranges)
- Select the header cell, plus the 3 cells below that and turn it into a Table (make sure to check the option "My table has headers")
- Give that table a name that makes sense (i.e."tbl_fruit")
- Go to the Formulas tab, and select Name Manager
- Click "New" button
- Give your list a Name that makes sense (i.e. "list_fruit")
- Give the Refers to field this formula: =tbl_fruit[name]
- Click OK
You can then use that "list_fruit" named range as the Source when setting up your Data Validation list (i.e. =list_fruit). Since the list refers to the table named "tbl_fruit" now, any time that table changes, the results from named range "list_fruit" changes automatically. The named range is the table.
Awesome. Just what I needed. Thank you.
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.
There is a much cleaner implementation that always uses the full set of rows in a column of a table and avoids the need to define named ranges which become a hassle to keep synchronized with table contents. Use an INDIRECT() formula in the Source field.
Source:
=INDIRECT("TableName[ColumnName]")
For example. Create a table named TYesOrNo.
Name the first column with a standard name, such as ID.
Make this column the first column in your table and you now have the basis for easy VLOOKUP() formulas to pull other parameters from the table columns based upon the field that is constrained to have a value that exists in that table.
You can also make the column itself a formula that produces a unique value from one or more other values in the table row. In this case, the ID field is a calculated field using just the Code, =[Code]. It could just as easily be changed to =[Code] & " - " & [Description], such that the dropdown values would contain Yes - Definitely Yes and No - Definitely No, or any other calculation that produces a useful and unique value for every row.
If you add a Sort column, you can easily sort the lookup table when you make changes to make the dropdown picklist occur in the order you want regardless of the alphabetical sort.
Define the Data Validation as an INDIRECT() reference to the table and unique identity column:
Here's what the dropdown list looks like when applied to a column of another table:
If you use a standard ColumnName (for example, ID or Code) for the unique value in all of your lookup tables then the formula only needs to have the TableName modified. Copying and pasting the Data Validation formula to re-use it for other fields is simpler with only one name to edit. Editing this field is a pain because the arrow keys produce formula references instead of moving the insertion point.
It took a long time for me to find this solution. I hope you find it useful.
Trying a second posting because the first one was flagged as spam.
There is a much cleaner implementation that always uses the full set of rows in a column of a table and avoids the need to define named ranges which become a hassle to keep synchronized with table contents. Use an INDIRECT() formula in the Source field.
Source:
=INDIRECT("TableName[ColumnName]")
For example. Create a table named TYesOrNo.
Name the first column with a standard name, such as ID.
Make this column the first column in your table and you now have the basis for easy VLOOKUP() formulas to pull other parameters from the table columns based upon the field that is constrained to have a value that exists in that table.
You can also make the column itself a formula that produces a unique value from one or more other values in the table row. In this case, the ID field is a calculated field using just the Code, =[Code]. It could just as easily be changed to =[Code] & " - " & [Description], such that the dropdown values would contain Yes - Definitely Yes and No - Definitely No, or any other calculation that produces a useful and unique value for every row.
If you add a Sort column, you can easily sort the lookup table when you make changes to make the dropdown picklist occur in the order you want regardless of the alphabetical sort.
If you use a standard ColumnName (for example, ID or Code) for the unique value in all of your lookup tables then the formula only needs to have the TableName modified. Copying and pasting the Data Validation formula to re-use it for other fields is simpler with only one name to edit. Editing this field is a pain because the arrow keys produce formula references instead of moving the insertion point.
It took a long time for me to find this solution. I hope you find it useful.
Hi Willy -
Threadsafe refers to processing that occurs in parallel on two or more processors in the same application. It is relevant in very fast, real-time applications where two events that need to occur in series, one after the other, can occur in the other order under some circumstances. Although I didn't read the article in great depth, it appears that the INDIRECT() function in Excel could exhibit a problem in a custom-coded application.
What the INDIRECT function is doing is to use a named reference to return the associated region of cells in the document for use by the data validation feature. I do not believe that there is any risk in using the INDIRECT function for this purpose. At most, it would interfere with the validation rule in an unusually-rare circumstance which would be very, very unlikely to happen again.
If custom Visual Basic for Applications code were being written that were making calls to other Application Programming Interfaces (API's) and the calls were being allowed to run on multiple threads, the use of this function might not be a good idea. In my opinion, there is no risk in using INDIRECT() as I have described it within native Excel formulas.
