Based on Tracker ‘Volunteers’ 17/08/2018

The Problem

You have distributed 100 templates to all the department heads as part of the annual budgeting exercise. You have just discovered errors in several formulas. You need to fix them.

The Issues

You can’t just fix them on your master and redistribute. The department heads have already been working on them for a week. So if you send them new templates they’ll have to start over.

If it was one or two you could send them instructions on fixing them. But there are too many errors to fix on too many sheets. Let’s say 20 errors on 5 sheets x 100 users = 1,000 formulas need replacing.

Solving the Problem

If we can identify the errors, and which cells on which sheets need new formulas, then it’s a matter of how to get those new formulas into the relevant cells in the spreadsheets the users already have.

[Diagram]

In principle, there are 2 physical parts and 1 logical part.

• The distributed spreadsheet
• The list of new formulas
• Logical part: A mechanism for updating the distributed spreadsheets with the new formulas.

Think of the list of new formulas in this way.

[Image] a single table of Sheet, Cell, Formula.

Now all we need is a way to

• Get the list to the users
• An automated way to go down that list, find the sheet, find the cell, and enter the formula.

We can get the list to the users via a CSV file.

But the method I describe here is cleaner, has less moving parts, therefore more robust.

The Solution