Excel Formula Help - Data Validation for Unique Entries
Following on from a recent blog posting made here showing how to extract unique entries from a list of data, we continue the unique data theme by showing you a way to force users of a Microsoft Excel to add only unique entries, thereby avoiding mistakes and repetition.
First of all, we have a list of invoice numbers; Of course, this could be very much longer. We start by selecting the cell where the next invoice number is to be entered and then selecting Data Validation from the Data Validation option on the Data Tools panel, on the menu bar, as shown here:
We are then presented with the Data Validation options box. On the Settings tab, Validation Criteria, you will need to select Custom under the Allow drop-down and under Formula, carefully enter =ISNA(VLOOKUP(A11,A$1:A10,1,FALSE))
Move to the third tab in the same options box, Error Alert. Here, the check box Show error alert after invalid data is entered, is selected. Also under Style, select Stop. For the Title, and Error message, the example in the image below is one option, or you can enter your own phrases.
Click return and then go back to the cell in question. Test the function by entering an invoice number that already exists. An error warning should appear, as seen below:
All that remains is for you to copy-down from A11 to cover the cells you wish to control.
The =ISNA(VLOOKUP(A11,A$1:A10,1,FALSE)) may seem complex but as long as the coordinates are correct it will work. Let's separate out each cell reference so that we can ensure accurate replication over any list:
A11 is the awaiting data cell, the first cell upon which this validation function will be performed.
A$1 reference's the first value from which the validation criteria with cross-check. It uses the $ absolute reference so that when our new validation criteria from A11 is copied down, it will always read from cell A1
A10 reference's the last value from which the validation criteria with cross-check.
Make sure these coordinates map to your list and all should be well. For more on how to use Data Validation with formulas, check out our Youtube video below or speak to our expert consultants. You can also visit the Microsoft help pages here.
