Spreadsheet Models for Managers

This reading is especially relevant for Session 5How to Merge Workbooks
 

When a group collaborates in developing a single workbook, and its members want to work in parallel, they need methods for merging the results of their efforts into a single workbook. Here’s a survey of available methods for merging workbooks.

Many spreadsheet projects in today’s workplaces are too complex to be developed by a single person working alone. Sometimes a single person could actually do the work, but it would just take too long. Teams can often do the required work faster and better than one person working alone can. In this course, many of you will be developing your course projects in teams, and you’ll face many of the same challenges that teams encounter in the workplace.

One of those challenges relates to the problem of merging the work teammates do independently of each other. That is, when they work in parallel on different versions of a single workbook, what can they do to bring it all together? This page describes a method for merging workbooks.

• Why you might want to merge workbooks
• The simplest case
• More complex cases
• A slight shortcut

Why you might want to merge workbooks

When several people work on a single spreadsheet project, we’d like them to be able to work simultaneously. If the project consists of a single workbook, as most do, then a problem arises: how can several people work on the same workbook at the same time?

The short answer is: they can’t. But Excel does have a way to address this situation. It’s called workbook sharing. It works like this:

• The original workbook is declared shared.
• Everyone gets a copy of the workbook and makes the changes they want to make.
• Periodically, by saving their copies, each teammate gets updates that show the latest changes everyone else has made.
• When they reach a point where they want to consolidate everything, or when they reach the end, they turn off sharing and Excel tries to merge all the changes.
• In the case of conflicts, there are some tools to help straighten things out.

It sounds pretty good, and for some projects, it works fine. But there’s a catch. Array formulas are not supported for sharing. To make a change in an array formula, the team would have to “check in” all their changes, turn off sharing, resolve any conflicts, save a new copy, insert the array formula, and restart sharing.

For a complete list of features not supported by workbook sharing see the following pages at Microsoft.com: Excel 2007, Excel 2010, and Excel 2013. Excel 2011 is probably similar to Excel 2010.

As we’ve seen in this course, array formulas are powerful and important. Until Excel supports them, Excel’s file sharing mechanism is too limited for most serious modeling efforts. We have to find another way.

This page describes an approach that will let a team work on a project together, provided they divide the work cleverly enough.

The simplest case

To explain the method, let’s start with the simplest possible case. Let’s suppose that Pooh and Piglet are working on a spreadsheet project together. Pooh is responsible for the worksheet that contains all the input streams and input parameters, and Piglet is responsible for the worksheet that does all the computations and produces the outputs. There are just two worksheets in the workbook.

The action starts when Pooh creates some of the inputs and parameters in a worksheet called Inputs, and a blank worksheet Outputs, and passes the workbook to Piglet, who then starts working out some of the computations and outputs.

As time goes by, Piglet decides to add a worksheet called Computations. Pooh adds a worksheet called Internals, which contains some additional parameters. Pooh adds some inputs and parameters to the Inputs worksheet, and adjusts some values of existing parameters.

Thus, the two of them are working in parallel, and their two workbooks are already very different.

The time comes when Piglet is ready for some of the new inputs and parameters that Pooh has implemented, so he would like to incorporate them somehow. Copy and Paste won’t work, though, because there are now some extra worksheets. (Even if there were only one worksheet, copy and paste still wouldn’t work in most circumstances.)

The first of the two workbooks is Pooh.xlsx. It contains three worksheets (Inputs, Internals, and the original empty version of Outputs). The second is Piglet.xlsx. It contains the three worksheets Inputs (the original version), Computations, and Outputs.

So, what do they do? Here’s the procedure:

• First they decide who will be merging the two workbooks. In our case, it’s Pooh. So Piglet sends Pooh his workbook.
• Next, Pooh saves copies of both workbooks in a special place to keep them safe, in case anything goes wrong.
• Next, Pooh opens Pooh.xlsx and deletes the worksheet Outputs, which is now out of date.
• Next, Pooh opens Piglet.xlsx, and moves or copies the worksheets Computations and Outputs to the workbook Pooh.xlsx.
• Next, Pooh activates Pooh.xlsx and uses the command Edit>Links… (Excel 2011) or Data>Connections>Edit Links (2007, 2010, and 2013) to open the Edit Links dialog.
• Next, Pooh clicks Change Source… To expose the Change Source dialog.
• Next, Pooh navigates to the workbook Pooh.xlsx, selects it, and clicks OK. If he’s asked for a specific worksheet, he selects the right one.
• Last, Pooh saves Pooh.xlsx

That’s it. He returns the result to Piglet, and off they go.

More complex cases

This method works for any workbook that can be divided into sets of worksheets that have dependencies that can be ordered, and provided the teammates don’t work on each other’s worksheets. It’s easy to understand this requirement if we consider a slightly more complicated case.

Suppose Pooh had added something to his Inputs worksheet that made it depend on something on the Outputs worksheet. In that case, the dependencies of his worksheets would form a loop: Inputs depends on Outputs which depends on Inputs. When he deletes the worksheet Outputs in preparation for inserting the version Piglet has been working on, the dependency is erased, and the merge fails.

Thus, if there is a dependency loop among the worksheets, a single owner must be assigned to that group of worksheets.

A slight shortcut

You can sometimes avoid merging by telling your teammates how to change their copies of your worksheets. For instance, if Piglet needs a parameter that Pooh hasn’t implemented yet, they have a choice:

• Pooh implements the parameter, and merges their two workbooks
• They both implement the parameter, avoiding the merge. At the next merge, Piglet gets the Pooh implementation, but in the meantime, Piglet uses the Piglet implementation.

This works fairly well for simple changes, but don’t try anything complicated — merging is always safer.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT