Making Data Universally Accessible to Workbooks

by Allen Wyatt
(last updated May 16, 2020)

4

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 Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Flipping a Drawing Object

Place a drawing object in your document, and it doesn't have to stay that way. You can flip a drawing object in either of ...

Discover More

Specifying Font Styles

Fonts, by default, come with one or more styles that define variations of how that font is displayed in your document. ...

Discover More

Getting Audible Feedback

You can add a bit of sound to your editing tasks by turning on Word's sound capabilities. This tip shows where this ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Noting When a Workbook was Changed

Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...

Discover More

Opening Multiple Workbooks at Once

Need to open a bunch of workbooks from within Excel? It's easy to do when you construct a selection set in the Open ...

Discover More

Printing Workbook Properties

Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 4?

2020-05-18 14:28:30

Roy

Sure, putting the table into Access, no extras, no normalizing, just essentially the current Excel table, then using PQ to access it seems ideal. Hard to argue with, even for a "I opened the Access program once, had no clue" kind of guy or gal.

But if one wants to do it in Excel alone, and the table is not interactive ("he" (above) will edit it from time to time suggests one cannot make a PO for a new vendor adding the vendor to the table either from the PO information (SURELY not enough info anyway!) or by the individual before making out the PO), then a simple FILTER() function will read out the current table when the PO file is opened and the file can go from there. Also, it will update when calculation occurs so new material can be available constantly.

If not all fields are to be seen by a PO-maker, INDEX() can give the acceptable columns. Just slightly more complicated. (FILTER() could do so, if all the acceptable columns were in a contiguous collection.)

But yeah, even if PQ is just reading that Excel file, it'd be easier once set up. But FILTER() is easier than PQ, for the busy, harried fellow, and both are easier than contemplating Access ("contemplating"... anyone who's used it knows the simple, single table would be no trick at all, but before doing it, it doesn't look that way at all... it ain't friendly looking and after creation, there's populating, and adding fields someday...).

My understanding has long been that Quicken makes its data pretty accessible, so I am surprised to not see a definitive PQ data source for it. Perhaps the misunderstanding is wrong. Or maybe it is an SQL database and doesn't need special mention. If it IS available live, then $250 for Quicken and one has a friendly interface but still a database, and might find other uses.

For instance, periodically importing the PO spreadsheet data into Quciken's PO function, and deleting the spreadsheets, giving the users a lookup spreadsheet that finds the PO they desire after the orginal's deletion, that might suit him well too. And the users would still have the familiar format but still gain. Who wouldn't rather enter a number and pop up his data versus opening a directory with 12,000 PO's to find his, then open it? That's work with Access as well, although it'd need a second table...


2020-05-16 15:13:04

Mark

@Ron S MVP - Hooray - Another Power Query user!

Your tip is great and I wholeheartedly agree with your suggestion to not store the information in Excel but rather in a database.

I like many of these tips but many of the problems posed really cry out for a more well thought out solution.

I will admit to solving problems with Excel myself (or if it's unavoidable Access) because it that's the only tool I had available and getting the company Information Services involved is either impossible, impractical, or would result in a mouse designed by committee (you know the old joke, what's an elephant? A mouse designed by committee (or IS).)


2020-05-16 14:53:13

Brian Lair

Seems like this would be a good use for Excel's data queries & connections feature. That way, the PO data could be stored in a separate workbook and automatically kept up to date in the workbooks that need to look up POs.


2020-05-16 07:51:40

Ron S MVP

There is another option, which I'm only a little surprised that no one mentioned. That is PowerQuery.
.
In the "template" you create a "Query" in PowerQuery to retrieve the vendor list. It could be in a standalone spreadsheet with just the Vendor information, or a database table (a little more stable, if you have the option of using an existing DB).
.
Define the Query to refresh on file open.
.
This way each copy of the file will have the query built in to it.
.
.
(Over 80) External Data sources available in Power Query.
https://d13ot9o61jdzpp.cloudfront.net/images/pq_get_data.png
https://www.myonlinetraininghub.com/power-query-and-power-pivot-definitive-guide
.
.
(Ignore the charting part of the tip)
Power Query Pivot Chart
https://www.myonlinetraininghub.com/excel-power-query-pivot-chart
We can create an Excel Power Query Pivot Chart. Strictly speaking it’s a regular chart based on data pivoted in Power Query. When your source data gets updated you can manually refresh the query or set it to auto-refresh.
. * Building the Data for the PivotChart
. * Manual Refresh
. * Auto Refresh Options


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.