THE EXCEL CHALLENGE – The UCLH Volunteer Management Problem

By Hiran de Silva

Part 1 — A Typical Collaborative Business Process

How do you build an Excel-based system that allows multiple people to work collaboratively, incorporates constantly changing external data, preserves locally entered information, and never loses an update?

This is a real-world Excel Challenge, based on a problem I encountered ten years ago.

It has recently resurfaced thanks to Tanya McIlravy, who sent me a video demonstrating Mark Proctor’s Self-Referencing Query technique in Power Query.

Watching that video brought back some fond memories. More importantly, it reminded me of a particularly good business scenario that illustrates something fundamental about Excel.

There is a world of difference between using Excel to maintain a spreadsheet document and using Excel to operate a collaborative business process.

And that difference is what this challenge is about.

A Hospital With a Personal Connection

Our story begins at University College Hospital in London, now part of University College London Hospitals (UCLH).

This hospital has a particular significance for me.

I was born there.

So was my son.

I also attended University College London for my BSc in Chemical Engineering just across the road.

The hospital I remember from my childhood has grown into a sprawling complex of buildings, covering a substantial area of central London.

It also has some fascinating historical associations.

Agatha Christie worked as a volunteer at University College Hospital during the Second World War.

George Orwell was a patient there towards the end of his life. He married while in hospital and subsequently died there in January 1950.

And in 1964, Ringo Starr famously suffered from tonsillitis, causing him to miss the beginning of the Beatles’ international tour. His tonsils were eventually removed that December.

But our story isn’t about famous patients.

It’s about the thousands of volunteers who help keep a major teaching hospital functioning.

And the spreadsheets used to manage them.

Six Thousand Volunteers. Two Spreadsheets.

Around 2016, my daughter was working as the manager responsible for volunteer administration at UCLH.

She inherited a system that had evolved over many years.

There were approximately 3,000 active volunteers, with another 3,000 volunteers on the records who were no longer regularly active.

That’s a substantial administrative operation.

Volunteers aren’t conventional employees. They indicate when they’re available, may work irregular schedules, and can disappear from active service for extended periods before returning.

Their records need to be maintained, their eligibility verified, and their assignments coordinated across the hospital’s many buildings and departments.

There are also important compliance requirements.

These include identity checks, immigration status, Disclosure and Barring Service (DBS) checks, and other approvals.

Such information isn’t necessarily static.

A clearance that was valid previously might expire, require renewal, or change.

The hospital must know about those changes.

And, of course, it must also know which volunteers are available, where they’re assigned, and what they’re permitted to do.

In the days before computers, much of this administration would have been managed through paper registers, filing systems and manual processes.

Eventually, somebody created a spreadsheet.

Then, as the organisation grew, the spreadsheet grew with it.

By 2016, the system consisted principally of two Excel workbooks.

Workbook 1: The Pipeline

This contained people applying to become volunteers, including those still undergoing the various checks and approval procedures.

Workbook 2: Current Volunteers

Once applicants had been approved, their records were transferred into the current volunteers workbook.

This second workbook contained approximately twelve worksheets, representing different buildings or locations across the hospital complex.

A straightforward arrangement, you might think.

And it probably worked reasonably well when it was first introduced.

But over the years, the volume of records, the complexity of the process, and the number of people involved had increased enormously.

The original spreadsheet design had not evolved to accommodate those changes.

Enter Trac — The External Recruitment System

The hospital also used an external recruitment platform called Trac.

Trac handled applications and recruitment-related administration, including the various stages of checking and approval.

The hospital’s volunteer management team could download application information from Trac as CSV files.

Each application had a unique application ID.

The downloaded data contained numerous attributes relating to the applicant and their application status.

This was useful.

But it introduced another problem.

The volunteer management team needed to bring this external information into their existing Excel workbooks.

New applicants had to be added.

Existing applicants might have updated information.

Some would progress through the approval process and eventually become current volunteers.

Meanwhile, the hospital’s own staff and volunteers were adding information to those same records.

And therein lies our first challenge.

The Problem With Updating the Spreadsheet

Imagine that you have downloaded the latest CSV file from Trac.

It contains information about applicants who are already in your workbook, together with new applicants who have just entered the pipeline.

Some information about existing applicants has changed.

You need to update those records.

You also need to append the new applicants.

But there’s a complication.

Your colleagues have already entered additional information into the spreadsheet.

Perhaps they’ve recorded a conversation with an applicant, entered an assignment, updated availability, or added an internal note.

What happens to that information when you refresh the external data?

Does it get overwritten?

Does it become misaligned with the correct applicant?

Do you have to copy and paste it somewhere else before refreshing?

How do you know which records are new and which already exist?

How do you ensure that you haven’t accidentally duplicated somebody?

And how do you know that an important change to an existing record hasn’t been missed?

These aren’t hypothetical concerns.

They are exactly the kinds of problems that arise when a spreadsheet becomes part of an ongoing operational process.

And they are central to the technique that Tanya brought to my attention.

The Self-Referencing Query

Tanya sent me a video in which Mark Proctor demonstrates a technique using Power Query.

It is commonly called a Self-Referencing Query.

The technique addresses two important requirements:

1. Preserving information already entered into an Excel table when external data is refreshed.
2. Incorporating new records without repeatedly adding records that already exist.

In the demonstration, these requirements are handled elegantly using Power Query.

Tanya also referred me to an alternative technique from Celia and a Microsoft article discussing a similar problem.

All of these approaches are addressing essentially the same business requirement.

And this is what caught my attention.

Because in 2016, when I was confronted with the UCLH problem, I approached it from a different direction.

Why?

That’s something I want to explore in the next instalment.

For now, let’s concentrate on understanding the business problem rather than jumping immediately to a particular Excel feature.

Because there is another requirement that we haven’t yet discussed.

And it is arguably the most important of all.

The Collaboration Problem

My daughter inherited a team of people who needed to maintain the volunteer records.

Some were permanent staff.

Others were volunteers themselves.

They worked different days and different shifts.

One person might update records on Monday, another on Tuesday, and somebody else later in the week.

And they all needed access to the same information.

But there was a familiar problem.

Someone else already had the workbook open.

The person attempting to update it couldn’t save their changes to the original file.

What happened next?

Sometimes they saved another copy under a different filename.

Sometimes they wrote down the changes on paper, intending to enter them later.

Sometimes the updates were simply forgotten.

And sometimes the next person had no idea that another version of the workbook existed.

Does this sound familiar?

Of course it does.

It’s one of the classic symptoms of what the industry calls Excel Hell.

But notice something important.

The problem isn’t that Excel cannot hold six thousand records.

Nor is it that Excel cannot perform the calculations.

The problem is that multiple people need to participate in the same business process, updating shared information without interfering with one another.

We have a collaborative process being operated through a collection of spreadsheet documents.

That’s a very different problem from manipulating data in a personal workbook.

“Daddy, Why Can’t Excel Work Like Salesforce?”

When my daughter took over the operation, she immediately recognised that the existing arrangement was inadequate.

She had previously worked with Salesforce.

That system had been configured to support the business process, with screens and interfaces appropriate to the work being performed.

Multiple people could use it collaboratively.

They could retrieve information, make updates, and continue their work without worrying about somebody else having a document open.

So she asked me a perfectly reasonable question.

“Daddy, why can’t the spreadsheet work like Salesforce?”

I found that question particularly interesting.

Because, although she knew I worked extensively with Excel, she didn’t know very much about the nature of my work.

For the previous two decades, I had been developing Excel-based solutions to precisely this class of business problem.

Collaborative processes.

Shared information.

Multiple users.

Controlled updates.

Centralised data.

Excel operating as part of a client-server architecture.

In other words, the very capabilities she associated with Salesforce.

I explained that Excel could indeed participate in such an arrangement.

But she immediately raised another very pertinent question.

“What about the information we’ve already put into the spreadsheet? Won’t that get overwritten?”

That question takes us straight back to Mark Proctor’s Self-Referencing Query demonstration.

And it also takes us into a much wider discussion about how we design spreadsheet-based business systems.

The Excel Challenge

So let’s turn this real-world scenario into a practical challenge.

We don’t need six thousand volunteers or thirty columns of information for the demonstration.

We can work with a much smaller dataset.

The principles will be the same.

We have an external source of structured data representing applications to become hospital volunteers.

Each application has a unique ID.

Some applicants are new.

Others already exist in our records.

Some attributes may change over time, including important approval and compliance information.

Meanwhile, our local users need to enter and maintain additional information.

And several people need to participate in the process.

Our solution must meet the following requirements:

Requirement 1 — Import external data

Receive updated applicant information from an external source, such as a CSV export from Trac.

Requirement 2 — Identify new records

Recognise applications that have not previously been imported and add them without duplication.

Requirement 3 — Update existing records

Recognise changes to information already held and apply those changes to the correct applicant.

This is particularly important for compliance-related information.

Requirement 4 — Preserve locally maintained information

Information entered by our own users must persist when the external data is refreshed.

It must remain associated with the correct applicant.

Requirement 5 — Support multiple users

A dozen or so people, working at different times and potentially simultaneously, must be able to retrieve and update information without interfering with one another.

Requirement 6 — Keep the process simple

These users are not Excel specialists.

Some are volunteers who may only work occasionally.

We cannot expect them to undergo extensive technical training.

The system must be straightforward to operate.

Requirement 7 — Maintain reliable information

We need confidence that updates have not been missed, records have not been duplicated, and important changes have not been lost.

And ideally, we should know who changed what and when.

Now, How Would You Solve It?

This is where I would like readers to participate.

We have a business problem.

We have a set of requirements.

We have Excel.

And we have a range of technologies and techniques available to us.

Mark Proctor has demonstrated one particularly interesting technique involving Power Query.

But is that the complete solution to this business problem?

Does it address every requirement?

Are there other approaches?

What would happen if we changed the number of users from twelve to a hundred?

Or the number of volunteers from six thousand to sixty thousand?

Would that change our thinking?

And, perhaps most importantly:

Should we start by selecting an Excel technique, or should we start by designing the business process and its information architecture?

That is the question I want to examine.

Not because one technique is necessarily right and another wrong.

But because understanding the reasoning behind a solution is far more valuable than simply learning the steps required to reproduce it.

Coming Next — The Thought Process

In the next part, we’ll examine the possible approaches to solving this challenge.

We’ll look at what the Self-Referencing Query technique achieves, what problems it is designed to solve, and how it fits into the wider requirements of a collaborative business process.

We’ll also examine alternative architectures, including the approach I was already using when this problem came to me in 2016.

And we’ll consider why the distinction between a spreadsheet document and an enterprise process matters so much.

There is a reason why I regard this as an excellent Excel teaching example.

It connects a familiar spreadsheet problem with a much broader question about the capabilities of Excel.

A question that is rarely explored in mainstream Excel education.

My thanks to Tanya McIlravy for sharing Mark Proctor’s demonstration and prompting me to revisit this experience.

Tanya mentioned that she has seen some of my articles and videos about enterprise Excel but hasn’t always understood what I am describing.

Perhaps this practical challenge will help bridge that gap.

Because we’re going to begin with something familiar.

A spreadsheet.

Some imported data.

Some information we want to preserve.

And a group of people who need to work together.

The challenge is not simply to make the spreadsheet work.

It is to make the business process work.

To be continued — Part 2: Examining the Options and the Thought Process.