Advertisement
 

To print: Select File and then Print from your browser's menu

--------------------------------------------------------------
This story was printed from ZDNet Asia.
--------------------------------------------------------------
Prevent duplicates when entering data into Excel
By Susan Harkins
Thursday, July 09 2009 11:19 AM
URL: http://www.zdnetasia.com/techguide/office/0,39044682,62055870,00.htm

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:

  1. Select the range of cells in which you want to prevent duplicate values.
  2. 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).
  3. Click the Settings tab.
  4. Choose Custom from the Allow drop-down list.
  5. Enter a formula in the following form into the Formula control:=COUNTIF($range,firstcellinrange) = 1

  1. Click the Error Alert tab.
  2. Enter the text Duplicate Entry in the Title control.
  3. In the Error Message box, enter a meaningful description, such as The value is a duplicate value and therefore, not valid.

  1. 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:

  1. Enter the text, including the blank line between the two bracketed components, and then select all three lines.


  1. 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.

  1. Delete all but the most necessary characters. In this case, delete l>.
  2. Preview the entry to make sure all of the text is right.
  3. Click OK to add the AutoText entry.
  4. 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 report

Reports 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 = True

End Sub

Be 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.