Using another spreadsheet as datasource is one of the easiest dataloads to set up.
Why?
“What’s the point?” you might ask. Well, from a business point of view, it may have some advantages to keep the data source and data analysis apart. A few:
1) Security reasons. Situations where you need to add personal comments, -analysis and/or –calculations, which is for “your eyes only” or just to keep your own files, which others can’t edit.
2) Reduce workload. As mentioned in a prior post, the major advantage is the fact, that you only have to set up the analysis once, and then simply update the data ever after. No reason to explain the advantage in this solution.
3) Multiple data entry. In many cases you need to collect data from various people or colleagues (holidays, working hour registration, travel expenses, etc.) or systems (fx. logs of all sorts). In the cases where the number of people and data are small, a spreadsheet is a fine solution for this kind of data collection. With this approach, you have an “external” “data entry platform” (A) in which you can administer read/write access easily (through folder security), and a detached “data consolidation platform” (B), where you make your analysis.
How – working example?
Situation: you are a leader of a sales team, who wants an easy overview of the mileage your team logs on an ongoing basis. Your goal is to get a picture of the number of miles each team member drives every week.
Example of the mileage log:
Walk-through:
1) Open an empty spreadsheet.
2) Choose the "data"-ribbon and click on "from other sources" ("external data"-group)
3) Choose "Microsoft Query"
4) Choose “Excel files*" (Note: be sure to mark “Use the Query Wizard to……”
5) browse your way to the spreadsheet (datasource) -> click "ok"
6) now you see all the sheets in the workbook (note: if you do not see any tables, click “options”-> mark “system tables”->click “ok”). Open the relevant datasheet (click on "+") and choose the columns needed.
In this example I will only be needing the columns:” Date”, “Person”, “Mileage”
7) click "next" through the guide -> click "ok"
8) in the popup "import data", click "pivotdiagram and pivottable"
9) now you setup your pivottable/chart for further analysis.
a. Add week or date in “axis fields”
b. “sum mileage” in values
c. “person” as filter
d. (layout is out of the scope of this post, but please ask me if you have any questions.)
And the final result:
10) finally you want to update your spreadsheet automatically everytime you open it:
Go to the ribbon "Data" -> click on "connection" (now you see the dataconnection you have created)and choose your dataconnection -> click on "properties" ->
choose the tab "use" and set a mark in "update data, when you open file" -> click "ok" -> click "close".
Now your pivottable and chart will update with the latest registrations everytime you open it.
Enjoy!