Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 37 Automatically Add Data to a Validation List

The validation feature in Excel is great, but
there is one key thing it cannot do (without the following hack):
automatically add a new entry to the list being used as the source
for the validation list.

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

Assume you have a list of names in the range A1:A10, as in Figure 2-24.

Figure 2-24. 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 2-25 (note the relative reference of A10):

=IF(OR($D$1=",COUNTIF($A$1:A10,$D$1)),"x",$D$1)

Figure 2-25. 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 2-26, then
click Add, and then OK:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x"),1)

Figure 2-26. 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 2-27.

Figure 2-27. 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 Figure 2-28.

Figure 2-28. 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.