If you have used validation, you know it's a neat feature. Perhaps most impressive is its ability to add a list to any cell from which the user can then select. Wouldn't it be nice if, when you enter a new name in a validated cell, Excel automatically adds it to the list? This is possible, thanks to the following paragraphs.
Assume you have a list of names in the range A1:A10, as in figure.
Figure. Workbook set up for validation list
These names represent employees in a company. It is not uncommon for new employees to be added to such a list, but at present, the only way to achieve this is to add the new names to the end of the list and then select the new names from the list in the validated cell.
To overcome this limitation, follow these steps. In cell A11, enter the following formula and copy it down to row 20, as in figure (note the relative reference of A10):
Figure. List with formula added to rows A11:A20
Now select Insert » Name » Define, and in the Names in Workbook: box, type
MyNames. In the Refers To: box, enter the following formula, as shown in figure, then click Add, and then OK:
Figure. Making the list dynamic
Select cell D1 and then select Data » Validation. Select List from the Allow: box, and in the Source: box, type
=MyNames, ensuring that the In-Cell drop-down box is checked. Click the Error Alert page tab and uncheck the "Show error alert after invalid data is entered" box. Now click OK. You'll see the result in figure.
Figure. The list with validation added to cell D1
Right-click the Sheet Name tab and select View Code. Enter the following code:
Private Sub Worksheet_Calculate( ) On Error Resume Next Application.EnableEvents = False Range("MyNames") = Range("MyNames").Value Application.EnableEvents = True On Error GoTo 0 End Sub
Close the window to get back to Excel and save your workbook. Now select cell D1, type in any name that is not part of the list, and press Enter. Select cell D1 again and look at the list. The new name should be part of it, as shown in the figure.
Figure. The list after adding a new entry to cell D1
If you want to add more than 10 names to your list, just copy the formula down past row 20.