Current Special! Complete Excel
for Excel 97 - Excel 2003, only
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
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
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
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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates