Combining Multiple Workbooks

[EliseFreedman]

Hi There

The department that I work for has several different systems - we regularly run compliance reports which are in excel format. It has got to the point where we have several different excel reports to cover different aspects of the systems.
For Example Driver Training, Manual Handling Training, Workstation Training, Non compliance with closing off actions in another system I was hoping to be able to create a single excel dashboard which would basically allow the user to go into a single workbook and be able to pull up data for whatever system they want to report on. Ideally they could chose to report on compliance with a single system or all the reports would be combined into a single report.

Can anyone suggest the best way to do this.

 

[Andrzejek]

You mentioned "several different systems" - does it mean the data is kept in different Excel files? In different data bases? In one data base (one would hope for)?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[EliseFreedman]

The data is in several different databases. They all have the capability for outputting data to excel so ideally I would like to be able to pull all the excel output into one single interface, A couple of the excel reports were previously a single report but management decided that we were better splitting them down into individual reports (which I totally disagree with as managers don't want to be consulting multiple reports)

 

[SkipVought]

You can have one workbook accessing data from different databases, from which a single dashboard could report on any or all interactively.

A dashboard could also be tailored to report by one or more user-selected criteria.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EliseFreedman]

Hi Skip
That's the kind of thing I was wanting. Can you point me in the direction of any resources that would teach me how to do this.

 

[SkipVought]

I think of it in these sections:

Data acquisition: one table per sheet. Usually done as a query/import via MS query.
Data manipulation: the acquired data is often combined via a query (MS Query) or summarized in some intermediate process.
Reporting: the final summarization and presentation of the data in tables, charts and other visualizations.

All of these are intermediate to advanced Excel skills; lots of moving parts, so to speak. You ought to develop proficiency in data acquisition: (Data > Get External Data; Insert > Tables), data manipulation: (Data > Sort & Filter; Data > Data Tools; Formulas: Statistical, Lookup), Reporting: Insert > Tables > Table; Insert > Tables > PivotTable; Insert > Charts)

The only resource, other than HELP, that I've used is Excel Charts by John Walkenbach.

My advice is to build piece by piece. Get one report working and then add another and integrate. Modify along the way as you discover methods that will simplify integration. I've redesigned many times as I discovered that a new method or approach would simplify things.

Use Structured Tables (ST). There is so much good stuff built into ST features.
Use Named Ranges in places where you are not using ST. Minimize the use of A1 or R1C1 referencing.
Learn how to leverage the SUMPRODUCT() function to perform multiple criteria aggragations.
Learn how to use INDEX() & MATCH() rather than V or H lookups.
Learn VBA. Use the macro recorder. Record SMALL steps of your process. Then look at your recorded code. Understand how that part works. Learn how to modify the code to make it workable. Learn how to integrate these litte pieces to create a useable program. Use forum707 and forum68.

Use Tek-Tips to help you grow your proficiency. I (we) love to help!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Having excel 2013-2016 I would use Power Query (built-in feature in 2016, free ms add-in in 2013) for data acquisition and manipulation. It's much more flexible and powerful than MS Query, still existing in excel. Final queries can exist as input for pivot tables or outputted as structured table. If all data has the same structure, there is no problem to join all to single query.
The whole process could be:
1. link to external data sources (via Power Query),
2. process data (Power Query queries),
3. reporting via pivot tables and charts, tables and charts from tables based on query.
Power query works (worksheet data input and output) on structured tables.

combo

 

[simian336]

Sounds like you are pretty failure with Excel and Databases. You might want to Consider MS Access if you have that available? You can import all you database tables from the different sources. Then query/report them fairly easily. You can build forms to automate the process.

Simian

 

[EliseFreedman]

Unfortunately I don't have MS Access Available in here

I looked into Power Query but we are still Office 2010 here. I tried installing the add in but it wouldn't let me (damn work firewalls). Looks like its going to have to be Getting clever with linking external data etc

 

[SkipVought]

MS Query does not "link" to external databases like when the external data source changes, the data shows up in Excel.

However, you do establish a connection string to the external data source and a query, that can be REFRESHED on demand. Once any query is defined, it can be simply be refreshed to import current data.

I usually controll all the data acquisition activity via VBA. But it can be as simple as refresh all queries when workbook opens; ie no VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DjangMan]

Just to add - it is possible to have a VBA macro take copies of worksheet(s) from different workbook(s) and put them into a new workbook. Like photocopying a bunch of different reports and putting them into a new folder. It isn't dynamic and isn't fancy but might do the trick.

 

[SkipVought]

FYI, data tables from other workbooks or data tables from other sheets in your workbook can be accessed via MS Query.

BTW, if you have functional sheets in other workbooks that you'd like to use in your master reports workbook, you could MOVE the sheet(s) to your master. Be sure to check formulas that link to other sheets in the sheets you move, in order to verify that they function as expected. Ideally ALL formulas in the master workbook ought to only reference cells within the master.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[simian336]

You can use VBA and ODBC to import data from databases directly into Excel. I generally create a button an put it on the screen. Not as flexible as Access but it would put all you data in one workbook.

Simian