Prevent duplicates when entering data into Excel
Thursday, July 09, 2009 11:19 AM
Duplicate values, when not valid entries, return erroneous summaries and totals. Here's what to do to prevent duplicates in a specific range using Excel's Validation feature.
Microsoft Excel
Prevent duplicates when entering data into Excel
Duplicates aren't always a bad thing--sometimes they're valid entries. But when they're not, duplicate values return erroneous summaries and totals.
The good news is that you can easily prevent duplicates in a specific range using Excel's Validation feature as follows:
- Select the range of cells in which you want to prevent duplicate values.
- Choose Validation from the Data menu. In Excel 2007, click the Data tab and choose Data Validation from the Data Validation option's drop-down list (in the Data Tools group).
- Click the Settings tab.
- Choose Custom from the Allow drop-down list.
- Enter a formula in the following form into the Formula control:=COUNTIF($range,firstcellinrange) = 1
- Click the Error Alert tab.
- Enter the text Duplicate Entry in the Title control.
- In the Error Message box, enter a meaningful description, such as The value is a duplicate value and therefore, not valid.
- Click OK.
If you attempt to enter a duplicate value, Excel will reject it. When this happens, click Cancel to clear the error message and enter a valid value.
Microsoft Word
Easy Autotext
During the run of any given day, I type the following two HTML tags several times:
<nl>
</nl>
<bl>
</bl>
I don't use them as HTML tags. Rather, I use them to mark the beginning and end of numbered and bulleted lists. It's a perfect job for AutoText, I thought, but I couldn't figure out how to enter more than one line in an AutoText entry. Tina Norris, a friend and colleague showed me how easy it is--enter the text and then select it, as follows:
- Enter the text, including the blank line between the two bracketed components, and then select all three lines.
- Check the text entry in the Enter AutoText Entries Here control. Word will use the first cluster of characters, in this case, that's <nl>, which is probably more characters than you'd like to enter.
- Delete all but the most necessary characters. In this case, delete l>.
- Preview the entry to make sure all of the text is right.
- Click OK to add the AutoText entry.
- From the Insert menu, choose AutoText. Then, choose AutoText from the resulting submenu. Word will insert the selected text, which you can check in the Preview pane.
To enter the numbered list tag in a document, simply type <n where you want the tag and press Enter. Word will insert the entire tag for you. It turns out that including additional lines in an AutoText entry isn't hard at all.
In addition, this method retains formatting (as you can see above). Apply the formatting as you normally would. Then, select it and work through the steps to create the AutoText entry--format and all.
Microsoft Access
Close an Access report automatically when there's no data to reportReports display data in a meaningful fashion, unless there's no data to report. When this happens, you'll want to usurp the reporting process a bit to avoid confusion. That's accomplished easily enough using the report's No Data event. Simply open the report's module and enter the following event procedure:
Private Sub Report_NoData(Cancel As Integer)MsgBox "There's no data to report.", vbOKOnly, "Error"Cancel = TrueEnd SubBe sure to save the form after adding the code.
When users open the report with no data to report, Access will display a meaningful message. (You can use whatever message you like, of course.) Users don't accidentally print useless reports or call you in a panic wanting to know what's wrong with the report.
0
0 votes
Talkback 0 comments
Featured Whitepapers
There are currently no comments for this post.