Simplify Your Life

1. Visualising Data in many columns

Client: Skin Cancer Researcher

This client has to record many items of data for each patient and for each skin cancer site. He was recording it in one row with many columns but it was difficult to see all the data for any one patient at one time.

I designed a worksheet to view one patient at a time. It uses VBA macros to record new data (into many columns of one row on the datasheet) and retrieve old data (from many columns of one row).

The data is stored in an efficient format and is now visible in an easy-to-read and easy-to-use format. This project was completed in 2 hours.

The final efficient design stores patient details in one worksheet and patient cancer sites in another worksheet.

One patient can be viewed at a time. Other patients can be chosen from a dropdown.

2. Merging Multiple Spreadsheets

Client: Government department

The client issues grants to organisations. Every 6 months, every organisation is asked to fill in and return an Excel spreadsheet report showing how they have used the money to help people in the community.

I designed the original report spreadsheet (sent to the organisations) and a summary spreadsheet with a button which runs a macro to read in all the spreadsheets from a folder, checks they are not duplicates and summarizes the data from all the different questions.

This solution saved the client many painstaking hours of doing this work manually and provided report-ready charts.

Some of the questions on the 6-monthly report that every organisation had to fill in.

The summary report had a sheet for each question showing the answers from each organisation together with a chart that could be easily copied and pasted to Word or PowerPoint.

3. Dashboard

Client: Call Centre

The client needed to show some stats on a dashboard in an Excel spreadsheet.

In the final design the client can choose the month from a drop-down and instantly see the stats for the relevant month.

The final design incorporates an Excel infographic-style dashboard. When a different month is chosen from the dropdown, all the numbers and graphs are updated.

4. Email from Excel

Client: Charity

The client keeps a list of all the external reports / licences / grant applications that they need to complete throughout the year. Once per month, multiple staff note their completion of these in a report for management.

I designed a sheet that used colours to show the number of days in advance of the due date. It also enabled reminder emails at the touch of a button, to be sent to the staff responsible for that report .

The compliance list includes all the reports, when they are due, when they have been submitted and a colour-coded number of days in advance/late.

Another sheet shows which reports are due soon or are already late, and an email can be written to the appropriate staff at the touch of a button.

Do you work remotely?

Yes, many of my clients, I have worked with for many years but never met in person. We work over phone, email and Skype.

What happens if I need to make a change later?

No problem. You can come back after months or years and just pay my hourly rate to get any changes made. My programming is also well commented and easily understood by another VBA macro developer if I am not available.

Will I be able to see the VBA code you create?

Yes. The code is yours. I can show you how and where to edit it if you wish to.

Can you use VBA in Excel online (in your browser)

You can open and change data in Excel spreadsheets with VBA macros but unfortunately, you cannot run the VBA scripts in the browser at the moment.