Excel Hacks Ebook [Electronic resources]

Raina Hawley, David Hawley

نسخه متنی -صفحه : 136/ 59

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.