Making Data Universally Accessible to Workbooks

Barnabas uses Excel to create purchase orders, one PO per workbook. He has created an Excel template that includes a table of all of their vendors. He would like to have this table, which he may periodically update, available to all of the POs, past, present, and future. It is available, automatically, to any new workbooks Barnabas creates based on that template, but not to any workbooks previously created. He wonders if there is a way he can have the current vendor list accessible to any of the POs.

Before discussing options, you need to understand that the idea of a template in the Excel world is much different than the idea of a template in the Microsoft Word world. (Boy, that's a tongue twisting combination—"Word world.") In Excel, a template is a starting point for a workbook. Once the workbook is created from the template, there is no more relationship between the workbook and the template on which that workbook was based.

Contrast this with Word, where a template is a starting point for a document, but a relationship is maintained between the document and the template on which it is based. You can update the template (style changes, macros, etc.), and any document based on that template will inherit those updates.

So, where does that leave us with Excel? It means that while you can update the template, those updates have absolutely no effect on workbooks based on that template. Well, no effect on workbooks previously created using that template. Those workbooks created after the template updates will be based on the updated template.

The most logical approach using Excel would be either (a) to maintain the vendor data in a workbook worksheet and periodically update that data using a macro, or (b) place the vendor data in its own workbook—perhaps stored on a network drive—that must be opened whenever the PO is open.

Which approach you use (a or b) depends on what you view as the best long-term solution for the way you use the vendor data and the PO workbooks. The implementation of the two strategies is probably too complex to go into here, as any such implementation will depend heavily on how you do the work that you do. It is an easy enough process to plan out, however. Let's say that you want to use the first strategy. Your steps might include the following:

• Determine where the template containing the vendor data table is stored.
• Create an auto-open macro that runs when the PO workbook is opened. It determines if the template is accessible and if the data in the template has been updated, perhaps using a revision number of some type.
• If the vendor data has been updated, delete the vendor data in the PO workbook and copy the vendor data worksheet from the template.
• Update the internal revision number. (This might actually be a value stored on the vendor data worksheet.)
• Close the template, as it is no longer needed at this point.
• Save the PO workbook.

If you wanted to implement the second strategy, then you would still need to rely on some macros. They might include the following steps:

• Create an auto-open macro that runs when the PO workbook is opened. It determines if the vendor data workbook is open, which ensures its data is available.
• If the vendor data workbook is not available, display a message informing the user of the fact and refuse the go further. (A good idea is to simply close the PO workbook since the necessary data is unavailable.)

In this second scenario, you would simply need to make sure that the folder in which the vendor data workbook is stored in the XLStart folder. (Anything in that folder is automatically loaded when you start Excel.) Of course, if you want this to work on multiple systems, you'll need to figure out how to get the vendor data workbook into the XLStart folder of each of those systems. Again, this can be done, but it simply means more checks and figuring out what should happen to "make it so."

Regardless of the approach you choose, it is going to take some heavy-duty planning, specific to your computing environment and your business needs, and some dedicated development time.

At some point, however, you are going to have to face the fact that Excel might not be the best tool with which to manage your POs. You might be better off—and time and money ahead—to either purchase some specialized software or develop your own software that depends on a centralized database. While you can use Excel as a database, it is a poor substitute for specialized database software such as Access, Oracle, or even MySQL.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13760) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.