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

mattyj7183

New Member
Joined
Dec 28, 2015
Messages
10
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

Well-known Member
Joined
May 24, 2005
Messages
3,725
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,696
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.:p

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:
 

Forum statistics

Threads
1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top