Suppose you have a spreadsheet where you are entering a list of unique values, such as invoice numbers, customer names, etc. You don’t want to duplicate any of the data, as this would cause problems down the road, but at the same time the list might be too large for you to manually verify that no duplicate values have been entered.
You can use Data Validation to ensure that no duplicate values can be entered into your list. If a user accidentally enters a duplicate, an error message will pop-up telling them that they have entered an invalid value.
To do this, follow the steps below.
- Select the column where you want your list of unique values to appear (for illustration purposes we’ll assume it is column A).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: Custom.
- In the Formula box, type the following formula: =MATCH(A1,$A:$A,0)=ROW(A1) (if you’re using this in a different column than column A, change the “A1″ and “$A”’s in the formula to whichever column you’re using)
- Click OK.
Now begin to type your list of unique values. If you should happen to accidentally enter one twice, you’ll see the following error message pop-up:
You can click “Retry” to re-enter a value in the same cell, or “Cancel” to delete the duplicate value.
**UPDATE**
To have a “one click” availability to this tip, use the following macro code. Again, like the formula above, change the “A1″ and “$A”’s to whichever column you’re using.
Sub Duplicate_Validation()
'
'MACROS BY EXCELZOOM.COM
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MATCH(A1,$A:$A,0)=ROW(A1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
As discussed in a previous tip, adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.
The example in the previous tip emphasized the importance of having consistent information when summarizing information in a PivotTable. Another use is to store lists of information, which could be anything, but in this example, I’m going to use website addresses.
Say you do a lot of research on several different topics, and summarize the results in a different spreadsheet for each topic. You could store all the bookmarks in your web browser, and chances are you already do. The problem with this is that if you’re only using a handful of bookmarks for one topic out of the many you have saved, you could end up searching through a lot of bookmarks to find the sites you need.
If you instead take the time up front to compile a list of sites you use frequently for a particular topic, you can use data validation to display them in one cell on your spreadsheet with another “clickable” cell to open the site in your web browser.
To do this, follow the steps below.
- Select the cell where you want your list of websites to appear (for illustration purposes I’ll assume your list will appear in cell A1).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
- In the Source box, refer to the range of cells in the same worksheet that contains your website list, or refer to a named range elsewhere in your workbook.
- Click OK.
When you select cell A1, you should now see the list of websites when you click the arrow to the right of the cell. Now select cell B1. This is where we are going to put some text that becomes “clickable”, sort of like a link on a website.
In cell B1, type =HYPERLINK(A1,”GO”). Now whenever you change the website in cell A1, using the drop down list, you can click the words “GO” in cell B1 go to that site. It’s like having your own bookmark system in Excel!
Adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.
For example, say your worksheet asks a user to input information that later will get summarized in a PivotTable. If the user doesn’t enter the information consistently (i.e. spells out words some of the time, and abbreviates the rest), the information won’t show correctly in the PivotTable output. Adding the drop down list will limit the number of values a user can enter into a cell, and provide consistency to your worksheet.
To do this, follow the steps below.
- Select the cell where you want your custom list to appear (if you want the same list to appear in multiple cells, you can copy the list later).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
- In the Source box, type the items you want in the drop down list separated by a comma. Alternatively, you can refer to a range of cells in the same worksheet that contains your list, or refer to a named range elsewhere in your workbook.
- Click OK.
When you select the cell that now contains the data validation, you should see an arrow to the right of the cell. Click the arrow, to display the drop down list.