Forum Discussion
Consolidating and manipulating data from different workbooks into one workbook
Yes. I've created 2 different layouts for the Summary Report. Here are the questions that correspond with each of the 3 tabs.
1. How to consolidate this data into the appropriate cells in the Summary Report.
2. Could I use either an Outline, Function, Formula, Query, or ???
3. Can the Summary Report be linked to different sources so that it automatically updates?
Thank you!
My Questions:
which column will the test results be on Original Summary Report#1? Column C Row 2?
which column will the test results be on Revised Summary Report#1? Column D adjacent to SC Number?
Your Questions:
Yes. I've created 2 different layouts for the Summary Report. Here are the questions that correspond with each of the 3 tabs.
Q 1. How to consolidate this data into the appropriate cells in the Summary Report.
A 1. In the current state of the dataset as it is in your sample workbook using the Revised Summary Report#2, It looks like a Data Model would be most appropriate course to go forward with.
2. Could I use either an Outline, Function, Formula, Query, or ???
A 2. (See A 1.)
3. Can the Summary Report be linked to different sources so that it automatically updates?
A 3. Using Power Query + Data Model, Yes (query by folder)
- Yes, I agree that REVISED Summary Report#1 is the better of the two and yes, the test results will be in Column D. Could you construct and provide me with the query that will pull a series of test results from a different workbook? And explain "query by folder."
Thank you!Query by folder:
https://youtu.be/nPlrQUbEn4o
You can probably put all the test results into one folder and have power query combine all the results so you test Source will just be one query.
I will work on it including the instructions/formulas to normalize the final dataset.
As I was working on the dataset, I noticed that this particular item needs its own designation:
It should be designated AC-1.0 in order for it to be included into the data model without creating any ambiguities:
what are your thoughts
I have a question. Is this an error or not? The Control Family Number and the SC Number don't jive.