In recent posts, I've talked about creating links from your Excel sheet to data that resides in Microsoft SQL Server or Microsoft Access. As you've probably guessed, you can extend those techniques to connect to many data sources from your Excel sheet. This means that you can bring the full power of Excel data tools, such as charts, filtering and pivot tables, to any set of data that you have access to.
To get a full picture, you may need to combine data from two different sources. As an example, I have client who is a plumbing company. They have an Work Order database that contains information about each job that they perform, along with the client and invoicing information. A second database contains information about materials they purchase for the jobs.
It would be nice if I could combine data from both sources in a single report, chart or pivot. For example, I'd like to get the job's status and technician information from the Work Order database, and pull in the related information about the cost of materials, which is in the Purchasing database.
I can do that by linking both sources to Excel, and then using Excel's VLOOKUP function to connect them.
The Data
Let's look at the data that I'll be working with:
I'll create links to both data sources in Excel. Since the Purchase Order data contains too much detail, I'll create a summary table in Excel first, and then connect that data to the Work Orders, joining on the Job Number. My final design looks like this:
The Steps
My first step is to create an Excel sheet that links to the two data sources. I described those processes in previous blogs: Linking from Excel to Access and Linking from Excel to SQL Server.
Next, I have to summarize the Purchasing data. That data set has too much detail for my purposes here. It's got line-by-line details about each purchase order. Instead, I need to know the total cost of materials for each job, so I'll create a summarized version of the data.
One easy way to create a summarized view of my purchase order data is to create a Pivot Table on it. Mine looks like this:
If you don't know how to create Pivot Tables, you'll find a wealth of examples by googling the topic. They are remarkably simple to create, so you shouldn't have much trouble.
VLOOKUP
Now that I've got the data I need, I'll put it together by using Excel's VLOOKUP feature. This feature allows you to look up a value that resides elsewhere in the Excel file. Here's the Excel sheet I'm working with, which is linked to my Access database:
In cell G2, I'll add the VLOOKUP formula:
=VLOOKUP([@JobNumber], 'Sum of Job Costs'!$A$4:$B$8, 2, FALSE)
As you can see, the VLOOKUP function has four arguments:
[@JobNumber]: tells VLOOKUP which value to locate on the referenced table. In our case, we're going to take the Job Number and look that up in our Purchase Order data.
'Sum of Job Costs'!$A$4:$B$8: tells VLOOKUP where to find the reference table. We're telling it to go to the worksheet called "Sum of Job Costs", and look through the data in cells A4 through B8. Wondering about the dollar signs? Those are absolute references. I won't go into all of the details here, but suffice it to say that we're looking in those specific cells.
2: The '2 tells VLOOKUP that when it finds our job number on the Sum of Job Costs sheet, it should return the second column from that row. Which, of course, is the "Total Cost of Materials Purchased" column.
False: This argument tells VLOOKUP to find exact matches on the job number. Without it, VLOOKUP does something called a "range lookup", which means if it doesn't find the exact job number on our Sum of Job Costs sheet, it'll pick one that close. Obviously that's no good here. We need to be sure that it only includes costs for the specific job number. So we set this argument to false.
My worksheet now looks like this:
As you can see, the formula was automatically carried down to all rows in my dataset. That's because Excel created a table when I linked to the Microsoft Access data. It's now smart enough to understand that what I do on the first row applies to all rows.
Wrap Up
And that's all there is to it. I've now got live links to both my Access data source and my SQL data source. I skimped a bit on the VLOOKUP details, so if you have questions on how to use that feature, reach out either via email or in the comments.
In this example, I've connected my Excel file to two data sources, both of which are set up to automatically refresh every time I open the Excel file. In my next Excel blog, I'll look at the properties of those connections, where we'll see how to set it up to automatically refresh, and how to correct when the source file moves. To be notified when that post goes live, subscribe using the form below.
As you've seen, Excel is a great tool for connecting to your mission-critical data, allowing you to harness the full power of Excel's tool suite without copying and pasting data. If you'd like to discuss ideas for your Excel / Database integration project, set up a one-on-one discovery meeting with me at no cost. Reach out via email (kmartin@northportsoftware.com) or phone (978-582-9550), or fill out the contact form.
Kim
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions#learningneverstops#smallbusiness #excel #microsoftexcel#msexcel #excelformulas #dataanalysis
© North Port Solutions, LLC, 2023. All rights reserved.