Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Data Validation List - Automatically Add to a Data Validation List

| | Information Helpful? Why Not Donate.

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Microsoft Excel has a very neat little feature called Data Validation found via Data>Validation on the Worksheet Menu Bar. One of its best abilities is perhaps being able to add a list to any cell that the user can then select from. However, one thing it cannot do (yet) is have a new entry automatically added to the list that is being used as the source for the list.

Let's assume for a moment we have a list of names in the range A1:A10 that represent employees in a company. It is not uncommon for new employees to be added to this list. At present, the only way to achieve this is to add the new name to the end of the list and then select the new name from the list in the validated cell. It would be good if we could simply enter the new name in the validated cell and have it automatically added to the list! Well we can and here is how. Go here if unsure on Dynamic Named Ranges

Add any list of names or items to the range A1:A10 on any sheet.
Go to Insert>Name-define and in the Names in workbook: box type: MyNames
In the Refers to: box enter this formula: =OFFSET(Sheet1!$A$1,0,0,COUNtA(Sheet1!$A:$A),1)
Click Add then Ok.
Select Cell D1 and go to Data>Validation, select List from the Allow: box and in the Source: box type: =MyNames Ensure the In-cell dropdown box is checked.
Click the Error Alert page tab and uncheck the Show error alert after invalid data is entered box. Now click Ok.
Right click on the sheet name tab and select View Code in here paste the code below:

Private Sub Worksheet_Change(ByVal Target As Range)



Dim lReply As Long



    If Target.Cells.Count > 1 Then Exit Sub

        If Target.Address = "$D$1" Then

            If IsEmpty(Target) Then Exit Sub

                If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then

                    lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)

                        If lReply = vbYes Then

                            Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target

                        End If

                End If

        End If

End Sub

Click the top right X to get back to Excel and now Save.

Now select cell D1 and type in any name, that is NOT part of the list, and Enter. Select D1 again and look at the list. The new name should now be part of it!

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft