Wednesday, January 24th, 2007

Check for Duplicates in an Excel List
To check an Exel list for duplicate entries, first select the list and name it using Insert, Name, Define and call it ListToCheck.

Now, select the list (and, assuming the list starts in cell A2), choose Format, Conditional formatting and type Formula Is and then
=Countif(ListToCheck,A2)>1

Then click Format to apply a format and click Ok.

Now each cell containing a duplicate entry will be formatted so you’ll see it clearly.

Helen Bradley

Tuesday, January 23rd, 2007

Finding messages in Outlook
When you repeatedly perform a search to find messages matching a particular criteria, create a Search Folder so matching messages will be stored and updated permanently.

To do this, in Outlook 2003, locate the Search Folder entry in the folders list, right click and choose New Search Folder.

Configure the criteria such as mail from someone or including some particular word and click Ok.

At any time, open the folder to read matching messages.

Helen Bradley

Monday, January 22nd, 2007

AutoFormat an Excel cell

This Excel 2003 macro formats a cell depending on its contents when you type something in it. If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.

The macro uses the OnEntry event which fires whenever something is entered into a cell. Attach the macro to an Auto_Open macro to ensure it is run whenever the workbook is opened.

To create this, choose Tools, Macro, Visual Basic Editor and, choose Insert, Module to add a module to the current worksheet then type the code into it.

Back in Excel choose Tools, Macro, Auto_open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.

Sub Auto_Open()
ActiveSheet.OnEntry = “formatCell”
End Sub

Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = “Times New Roman”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub

Sub Auto_Close()
ActiveSheet.OnEntry = “”
End Sub

Helen Bradley

Sunday, January 21st, 2007

Smarter replacement in Word 2003

It is possible to use Word’s search and replace option to add text to the search text.

To do this, choose Edit, Replace and, in the Find What box type the text to locate. In the Replace with box type the text to add to the text you’re searching for and use ^& to refer to the search text.

For example, to replace Sydney with Sydney, NSW search for Sydney and replace with ^&, NSW.

Helen Bradley

Saturday, January 20th, 2007

Archiving messages in Outlook 2003

To ensure your Outlook .pst file stays trim and doesn’t get bloated with old and outdated emails, configure its AutoArchive options so that older messages are automatically removed or filed away.

To do this, right click the folder to archive and choose Properties, AutoArchive tab and select the Archive this folder using these settings option. Now enable the Clean out items older than and set the appropriate time period. Only select to Permanently delete old items if you really don’t want them saved. Enable, instead, Move old items to default archive folder and Click Apply.

If you haven’t got AutoArchive configured to run periodically, you’ll get a warning to this effect and Outlook will set it to 14 days by default. You can change the timing by choosing Tools, Options, Other, AutoArchive.

Now, every 14 days (or when you specify), AutoArchive will run and the old items will be moved to your archive folder. Your archive file will appear in your folders list so you can drag and drop messages from it back into your regular Outlook folder if there are archived messages you need to refer to.

Helen Bradley

Page 37 of 37« First1020303334353637