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