Automated importing
Many people like the external links facility in Excel and find it useful. However, there are problems:
- When you open a workbook you are asked whether you want to update the links, but can't see what they are or choose to update some and not others. To do this you have to use the Edit > Links... command.
- You can't tell when links were last updated.
- People working on the source workbook have no way of telling that another workbook is linked to it.
- Links can get broken if you edit the source workbook when the destination workbook is not also open.
- It is often difficult to track down exactly where the source workbook is being referenced in the destination workbook.
- External links may not work well with your team's method of keeping track of development and release copies of a spreadsheet.
An alternative that some people use is to copy data from one workbook and paste it into another. There are problems with this too:
- There is often no audit trail leading from the pasted information back to the original workbook.
- Copying and pasting, like all manual procedures, is subject to error.
- You can't tell when the copying and pasting was last performed.
- People working on the source workbook have no way of telling that its values are copied and pasted into another workbook.
- Copying and pasting may not work well with your team's method of keeping track of development and release copies of a spreadsheet.
One way around some of these problems is to have well defined export and import areas in your workbooks, preferably as separate sheets. You can then make it a convention that you can link to data only in an export area, and that the links should occur only in an import area, with a similar convention for copying and pasting. The remainder of your workbook is thus free of external links.
If you do this, it becomes much easier to introduce an audit trail: you simply include in the export area some indication of the parameters used for the calculations, and make sure that those values are imported too.
If you also confine yourself to using named ranges when importing through external links, you avoid some of the problems that can arise when the source workbook is edited. As long as the correct named ranges are present in the export areas the links should be trouble free.
Under these circumstances a viable alternative is often to use automated copying and pasting, which is what XLSior's automatic import command does. Automatic imports are defined on special import sheets, whose name begins with X~Imp. On each of these sheets you specify a range that should be imported from an external workbook. When you update the imports, the values from the external range are pasted into the import sheet. The time at which this pasting occurred is also recorded.
If you make sure that the ranges that you import in this way include all the necessary information about calculation bases, you thus automatically have an effective audit trail. The import sheet is protected in order to prevent accidental changes to the contents, and cannot be unprotected by the user.