Microsoft defines an external reference (or link) as a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook. Throughout this article, we’ll refer to the workbook that contains the links as the consuming workbook and the workbook that it’s linked to as the source workbook.
While external workbooks can be a useful feature, it is a fairly dangerous one as well. It can often prove more problematic than beneficial. The main issue with external references is that they connect to a source workbook in the file system by its physical file location. Since the consuming workbook is not aware of any changes in the file system, certain actions (such as changing the location of the source workbook or deleting the file) can cause major issues. The consuming workbook is still looking for the source workbook in the original location, so all of the cells that reference the links will begin returning reference errors.
Another issue can occur when the source workbook is updated. If any of the referenced links or ranges are inadvertently changed or deleted, this can also cause reference errors or – perhaps worse – inaccurate results where the reference cells no longer link to the intended locations. The latter can be a difficult issue to detect and diagnose since the destination workbook will still continue producing results that are simply inaccurate rather than clearly indicating that a problem exists.
SpreadsheetWeb does not support external references to workbooks on the file system; instead, it creates linkages through web service interactions using a SpreadsheetWeb Connector. The advantage of this approach is that it substitutes the problematic workbook references that can come from Excel’s usage with a web service connection that can clearly indicate modifications to the source file.
Example
Let’s take an example. Let’s say we are developing an application for an insurance agency selling term-life insurance products from three different insurance carriers. Each carrier provides the agency with an Excel spreadsheet calculating their term life premiums. Each time they update their calculation logic or rating factors, they provide an updated version of the workbook. We assume that each carrier’s workbook has similar input/output fields but may differ significantly in their calculation logic.
First, we deploy each workbook as a web service on SpreadsheetWeb. This will expose the calculation logic of each workbook through the SpreadsheetWeb API based on the corresponding inputs and outputs.
The next step is to create a Designer application where we aggregate the required inputs for each companies’ workbook. Since they all have a similar input/output structure, capturing the inputs once and mapping them to each workbook will be sufficient. For this, we will create a SpreadsheetWeb connector for each workbook and map their inputs and outputs (from the source workbook in Excel) to the inputs and outputs in the Designer application (the consuming workbook in Excel).
Next, we bind each connector to a button. Pressing the button will send the user-entered values to each of the connected workbooks and trigger calculations, mapping the calculated results to a set of defined outputs in the consuming workbook.
The screenshot below shows these three sets of disparate results aggregated side-by-side in a Designer grid control.
With SpreadsheetWeb’s external reference support, it is possible to create applications that simultaneously connect multiple workbooks to aggregate a variety of calculators into a single robust solution. Each workbook can be maintained and updated independently, while eliminating the well-known issues that external references suffer from in Excel files.
