Claire's IT Training and Solutions blog

Today, I delivered a purpose-built automated spreadsheet to a client. The client is a major publisher and the job was very complex: importing daily expenses claim data, processing it in several stages to ascertain what type to assign to each claim, then producing six different reports, each containing multiple types of claim. Finally I had to save an output file containing the data and all six reports, as well as saving two of the reports separately.

I finished the final tweaks and tests this morning and sent it through to the client. By return, I received this testimonial:

“I don’t want to sound too gushing but I just want to tell you how brilliant you are!

• I try to give clear requirements
• You question them when they’re not clear to you
• Then you follow the requirements and give me what I asked for
• You test it before you send it to me
• … and you’re very understanding when I change my mind

Sounds perfectly sensible but there are not many people out there who can do this.”

Well, I must say I was flattered, but in truth the same comments pretty much applied to the client. His original requirements specification was superb, he responded very quickly to any questions I had, he was patient when my first attempts had errors, and he communicated brilliantly at all stages.

As always, communication is key!

About two years ago, a visitor to my networking group, BSI Dunmow, spoke to me at some length about the work I do, specifically asking for some ideas on a complex mailmerge that she was doing using Microsoft Word. She comes from a fair way out of the area, so decided not to join us, but kept my card and recently got in touch again.

She produces legal documents for a solicitor which, up to now, she has been producing in Microsoft Word. These include a schedule of work done (stored as raw data in a Word table), then merged documents to produce a fair copy of the schedule, with a breakdown of total costs at the end typed in by hand, and individual information sheets for each item on the schedule and for each item in the totals. She also had a set of equivalent files for personal attendances.

Recently, it has been decided in the practice that they need to analyse the costs of items that fall into different categories – information that was not even in the raw data document. She rang me to talk about this, and I felt that now would be a good time to move the data part of the requirement into Excel so that it can be analysed.

Excel is somewhat lacking as a word processor, but I found I was able to structure the “raw data” neatly enough to be able to print out in the same way as her original merged schedule and attendance documents. With the help of some pivot tables, and some careful text manipulation using functions, I was able to add the breakdown of total costs at the end of each report, and another pivot table gave the category analysis. Finally, I tweaked her original merge document and got it to reference the new spreadsheet as the data source to produce the item-by-item pages.

There was one disproportionally difficult part of this. Because the work is done for a legal firm, dates and times are separated by full stops, not slashes and colons as they are in Windows. Formatting these in Excel was easy enough, but the number formatting in Word doesn’t work in the same way and it took me a while to work out the correct formatting for times – especially when I wanted to total time and it tried to show the result as days and hours!

Well, the Mac spreadsheet problem is sorted – but it turns out that I was on the wrong track when I first tried to fix it. When opening the spreadsheet to try and print invoices, we had noticed that sometimes, though not always, it complained about broken links. When I brought the client’s version of the sheet home, this turned out to be the case on my machine too. I tracked these down on my machine and found that a couple of (unused) range names were pointing to non-existent locations. Borrowing my client’s Mac so as to be able to work on the problem at my leisure, I fixed these – and lo and behold, the problem went away!

I find it difficult to see what the problem could have been exactly, but presumably it affected the internal structure of the PDF somehow and thus prevented its successful export. Anyway, it’s not best practice to have old, unused range names or references in a workbook – especially if they are no longer valid – so it was a good opportunity to carry out some essential housekeeping.

Today I worked with two clients who use Excel 2011 on a Mac computer. Personally, I use version 2010 on my Windows 7 desktop PC and version 2013 on my Windows 8 laptop, so it’s always a bit of a challenge for me to get my head around the way the Mac handles things differently, such as menus.

The first client was one for whom I had created a simplified spreadsheet for keeping track of income and expenditure on a number of different bank accounts, with entries analysed by account and category. The sheet was based on her original “account book” style sheet, but I made the entries a lot tighter and set up Pivot Tables to analyse the transactions in the way she required. I originally built this workbook for her back in January, but there wasn’t time for her to put all her 2013-14 tax year information in before the deadline on 31st, so she decided to leave the bulk of the data transfer till the new tax year – which brings us to now.

In the end, there was little for me to do. I just reminded her how to add new entries and also that if she wants to add new income and expenditure categories, these have to go into a separate lookup table, as I have used data validation to limit the entries that can be added to her main table. She is now happy to add her own entries and bring her financial record-keeping up to date.

The second client had a rather more serious problem. For some months, he has been very happily running a workbook I created for him with a macro-driven automated invoicing system. He simply clicks on a command button and Excel spits out all due invoices in PDF format. For some reason, this has suddenly stopped working. He has not changed anything, but there was an operating system update, which may have triggered this. It’s difficult for me to test, as the macro runs fine on my Windows PCs, so I have to go to his premises and use his computer.

Sadly, I was not able to fix it fully today. I quickly identified that the problem was in the part of the macro that saves the invoice into PDF format, but have not been able to get this to behave properly. I have left him with a work-around that uses an alternative method of producing the invoices, but this creates a separate PDF for each sheet in the workbook – so each time he creates an invoice he gets three or four unwanted PDF files as well and has to delete them. I am currently researching the problem and hope to fix it properly next week, but I will be delivering a new training course on Monday, so will need to spend some time preparing for that first!