Reverse Engineer a database - Referencing same data points in multiple workbooks not necessarily in same cells

[mattyj7183]

This one is starting to hurt my brain.

I have a workbook with multiple sheets and the point of the book as a template for a questioner/form. Each tab has various items to fill out or answer. Some basic info, some with dropdowns, and some referenced via formulas. Each client will have their own workbook, with their own data, which will result in about 800 individual files. The initial plan was to keep these workbooks separate, but it now seems that forming a database out of all the data points spread throughout the different tabs is worth pursuing. Generally, the same data points are in the same cells across the multiple workbooks (ie A2 of every "Client Profile" sheet contains the group name, A8=primary contact, A9=secondary) but there are cases where the data might not be in the same exact cell. In my "Cash Management" sheet I have a list of accounts with various details and not every client has the same number of accounts.

I was experimenting with the Query feature and I was getting stumped then I tried messing around with the name manager and that didn't work either. Ideally, I would like to have one master sheet that has all the headings of what I would like to pull from the other sub-workbooks. As the title says, I am essentially reverse engineering a database.

Is this possible?

 

[pbornemeier]

Sure it is possible, it will also be a lot of work.

If you don't publish and lock the input files so users cannot modify the format, at some point a file that used to work will fail to do so, but you many not find it out until too late. In the cases such as the "Cash Management" sheet ensure the format is such that it can be extracted programmatically with each block of data duplicates the standard layout.

Line 1 of Account Block: "ACCOUNT #"
Line 1 of Account Block: Column B contains a number that that user uses to distinguish an account
Line 2 of Account Block: "ACCOUNT NAME"
Line 2 of Account Block: Column B Contains the name of the account
....
Line 27 of Account Block: Column A: (some data identifier)
Line 27 of Account Block: Column B: (some data)

A tightly formatted input workbook will allow easier data extraction.

Rather than trying to pull this into a single Excel workbook, you may want to consider using Access to store the info in a "real" database with an Excel front end to simplify queries.

 

[SpillerBD]

The best route that I am aware of is using Tables and PowerQuery to append the Tables into one data set.
First trick is have PowerQuery. If you have Excel 2016, its been renamed Get & Transform. If you have 2013, you need to have a specific version of Excel. It is available for 2010 too but expect some caveats (If you have access to Power Pivot, best.) That will achieve One data source for reporting and will update with the PowerQuery/Data Refresh.
Also, Instead of having multiple sheets (800? Really?) you could work with Workbooks to group clients together or have a single workbook per client. At 800 I don't know any single way to handle thant many elegantly other than to not do it that way.

If you do not have PowerQuery, there is still a way to link the data for PivotTables under the data Model, but that end may be insufficient for your needs.
Access provides an Append methodology which you could use too...