mthomas.co.uk - IT Tips, Tricks & Tutorials

Using a combination of the ROW and MOD functions and Conditional Formatting, you can easily shade alternative rows in Excel, making a list of data easier to read. Read More

Later this year, the company that I work for will be upgrading to Vista/Office 2007. Earlier this year I produced a short video to show to my colleagues (we will be delivering the training), outlining the major differences between Outlook 2000 (the version currently used) and 2007.

Get the latest Flash Player to see this player.

Tonight I attended an online presentation which was run by the North West Adobe User Group.

The excellent presentation covered 2 subjects - Adobe Acrobat Connect (an online collaboration and web conferencing tool) and Photoshop Express - a free online image editor (you also get 2GB of space to store your photos online when you sign up for a free Photoshop Express account). Photoshop Express is not meant as a replacement for the full Photoshop product, however, it has some cool features including a touchup tool, recolouring, red-eye removal and colour and sharpness enhancements.

The presentation itself was delivered via Connect and having used similar tools (Centra and Webex), I can say that Connect provides the best user experience by far.

The MOD function in Excel returns the remainder after a number is divided by a divisor.

As an example, start a new spreadsheet and enter 172 into A1. This represents the number of minutes that a piece of machinery has been running (this figure is manually entered after the user has noted the value by reading the machine’s “Activity Log” which outputs a value in minutes).

Enter a formula in A2 to calculate the number of whole hours:

=INT(A1/60)

As we only want to display whole hours, the INT function is used to return the Integer part of the result (172 / 60 = 2.866667).

The formula in A3 which calculates the number of remaining minutes is:

=MOD(A1,60)

In other words, 172 divided by 60 is 2 remainder 52

When using Outlook with MS Exchange, you can open another user’s mailbox (providing that they have assigned permissions correctly). This is useful if you want to check whether a colleague is available..or you just want to snoop at an ex-colleague’s whereabouts!

However, Outlook remembers the names of the previous few users whose mailboxes you have opened (Click File > Open do display the list) - handy if you want to check the same user’s calendar again but could also land you in trouble if you’ve been snooping! Read More

Unfortunately at work I am limited as to the software I can use. I occasionally have the need to work with photos and if I’m lucky I may find that the PC I’m using has Paint Shop Pro installed but more often that not I’m reduced to using Microsoft Photo Editor.

There’s a known issue where, when you open the program or double click a graphic file, Photo Editor will start minimised. When you click on the “entry” in the Windows taskbar, it does not restore to a window. When you open Task Manager, it is running.

To resolve this:

Run Regedit (Start > Run > Regedit).
Find HKEY_CURRENT_USER/Software/Microsoft/Photo Editor/3.0/Microsoft Photo Editor.
Delete the key called “Initial Position”.

You may need to re-boot for the change to take effect

I was recently presented with an Excel file which displayed a message every time it was opened indicating that it contained links to other files. I was assured by the owner of this spreadsheet that all links had been removed.

Thanks to officearticles.com for helping me to solve the problem:

• Open the file with the problematic link in (MyFile.xls in this example).
• Save the file to a new name (like MyFile1.xls).
• Select Edit >Links and choose to link it to MyFile.xls.
• Save it and close.
• Delete MyFile.xls and rename MyFile1.xls to become MyFile.xls again.

Of course if you have Excel 2003 or later you can just select Edit > Links and there is a Break Link button in the dialog box!

In Word, selecting some text and pressing Shift+F3 will change the case of the selected text. It works on a cycle through the following - lower case , CAPITALS or Sentence Case.

Excel does not have this feature but you can select a cell and use =UPPER, =LOWER or =PROPER.

The format is =FUNCTION (CELL) where FUNCTION is the required function (upper, lower or proper) and CELL is the cell that contains the text.

For example, if A1 contains MIKE THOMAS, and in B1 you enter =PROPER(A1), B1 will contain Mike Thomas.

You can then use Copy and Paste Special / Values to copy the contents of B1 into A1, thus overwriting the original text.

I’ve used this feature many times for clients who have been given a list of, for example, names, that have been pulled off another system (say a payroll system) where the original data has been stored as capital letters.

Last year I wrote found a piece of VBA code (well I did find it but then adapted it so back to my original comment - I wrote a piece of VBA code) that split a Word mail merge output document (i.e. the one that is produced as a result of running a mail merge) into seperate files which could then be emailed to the recipients.

Well yesterday one of my colleagues asked me to dig out the code and apply it to his mail merge - and he’d make me a cup of his famous coffee if I could do it.

So 20 minutes later and with a few changes to the code, the job was done and I was sat back with a mug of steaming coffee.

And the code…You’ll find a downloadable zip file here.

“Excel.exe has generated an error” was the cry from the desk behind me. “I can’t open the file and it’ll take 2 days to get a restore”

Enter a knight in shining armour (or a least a shiny-screened MacBook). And what could Neo-Office (a FREE Office Suite) do that Excel couldn’t? Yes - it opened the supposedly corrupt Excel file. All that was missing was a few graphs - and they were easily re-creatable.

I’m not sure whether the young damsel in distress was more speechless about the recovery of her precious data or the shiny white laptop on my desk! Anyway, fact remains - Mac 1 PC 0.