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

mattyj7183

New Member
Joined
Dec 28, 2015
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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.:nya:

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... :eek:
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top