Shared Workbooks

Once an Excel workbook has become useful, it is likely to be copied. Maybe a Sales ledger workbook has proved useful in one office and there is then a temptation to copy the workbook to other offices so they can use the functionality. Unfortunately this means that any problems or improvements to the original workbook must be replicated across multiple copies.

The real problem is that the spreadsheet holds both the data and the functionality to transform it into something useful. We have seen that the PersisTables toolkit allows data to be secured and shared. So we can use these tools to extract the data from the spreadsheet so all that remains is the functionality.

This approach means that a single template workbook can be used on different data sets. By loading the data set appropriate for the user into the template workbook, it allows each user to access the data they want using common functionality.

Making the template workbook ‘read only’ means it cannot be altered by one user and saved, having an adverse impact on others. No longer will an overwritten formula accidentally saved cause a problem when the workbook is reopened.

This approach allows bugs to be fixed in a single copy of the workbook and for enhancements to be rolled out to all users simultaneously. A more detailed description of how a spreadsheet can be built which captures user input, retrieves historic inputs and allows the data to be saved is provided in the PersisTables User Guide available with the installed package.

Copyright © 2018 Persis Solutions Limited