The confusion of master spreadsheets

The confusion of master spreadsheets

Spreadsheets are great. They’re easy to set up, personalise, and use. Except when you want more than one person to access the information in the spreadsheet. That’s why it gets trickier.

However, there are a number of ways to work around the problem.

1. Use a master spreadsheet

This method doesn’t require much technical knowledge, but can be labour-intensive, especially if you work with big files.

To begin with, set up a spreadsheet with all the calculations and fields in place, laid out and formatted exactly as you want.

Give a copy (with or without data) to each person who needs to use the spreadsheet. Let them add or change data according to their requirements.

Once everyone has finished changing the spreadsheet, someone has to manually open each edited file and collate the information back into a single spreadsheet.

This master copy can then be copied and disseminated, starting the process again.

If you take this approach, it’s important to have a convention for naming the files so you don’t get confused. I tend to use a combination of file name, user name and date. For instance:

• ItemPrices_John_19.1.14.xlsx
• ItemPrices_Martin_21.1.14.xlsx

Sometimes I have to add the time to a file name too, like ItemPrices_Martin_21.1.14_11:33.xlsx.

2. Have one spreadsheet file on a server

If your business has a server — whether in your office or in the cloud — you can keep a single copy of the spreadsheet.

Because only one person can open the spreadsheet at a time, there shouldn’t be any data issues, because one person has to close the spreadsheet before another can open it.

However, you'll inevitably find that more than one person wants access to the data in the spreadsheet at any one time. So what happens is one of your team takes their own copy, changes it … then confusion reigns and you’re back at method one again.

3. Set up shareable spreadsheets

Spreadsheets are just tables, with columns (field names) across and rows (records) down.

Instead of storing your data in a spreadsheet, you can save it using database software. This still keeps your data in a table (or several tables), but is superior in several ways:

1. You are not visually limited to a grid format. You can take (some or all of) the fields and lay them out however you want. Perhaps as a form on the screen, as a report, as a customer receipt … or anything.
2. You can filter your records easily. Select data using almost any criteria you can come up with, then view only the matching records.
3. Lots of people can use it. Anyone can create a new record at anytime. Only one person can edit a particular record at a time, but their changes become immediately available to everyone.
4. You can save more data. In spreadsheet software, you usually end up using multiple spreadsheets when you have lots of types of data. However, a single database can contain several tables that are linked.

   For instance, you can have a table of customer details and a table of invoices, linked together.

The advantage of using a database like this is that you only ever have to enter information once. You can simply re-use it when you need to. That avoids cut-and-paste errors and eliminates worries about which copy of the data is the right one.

• There’s beauty in business data
• Keeping your data safe
• Customer relationship management software

 Martin Bridges is an expert in office admin and developing relational databases who works at dataBASED.biz.