Merge and Sum specific data from multiple workbooks into a single workbook

[jstrem75]

I have a data sub-folder that contains date format workbooks. (Tracker 12.01.2019.xlsx, Tracker 12.02.2019.xlsx, etc.)

I'm trying to pull specific data from each workbook, sum the data into weekly totals per employee, and add to a new workbook with a weekly drop down that can change the data accordingly.

Every Tracker data workbook is the same, with 3 sheets. (1st, 2nd, and 3rd) Another issue might be that the names are not always in order, so the data has to be pulled according to the name.

1st problem: Pulling the data, based on the employee name
2nd problem: Adding/Sum the data into totals
3rd problem: The weekly drop-down

I"m using Excel 2019, but the data and users who view and input the data are using Excel 2013.

I've tried a few functions and different methods, but I can't seem to get it working quite right. Any help would be appreciated.

[Glenn Kennedy]

Do you REALLY need to keep these in separate FILES? This is going to complicate the solution needed and be rather slow, if you have large bodies of data that you want to pull across.

The best solution involves the use of INDIRECT. But, it only works if BOTH source and destination files are OPEN. Will they be?

After that there are a few VBA-based approaches. These tend to be (very) slow. Are you OK with a VBA-based solution? How many rows/columns are there in your real data?

[peterrc]

Hi jstrem75,

Welcome to the forum.

Looking quickly at the data I think you could achieve what you want with Pivot Tables.
How do you feel about Pivot Tables?
The data in sheets 1 to 3 in each workbook are the same except the values in cols D to H.
Also you want Weekly Reports so what does the "12.01.2019-12-07.2019" in the Weekly Report Sheets (1 to 3) mean?

Regards

peterrc

[jstrem75]

Glen Kennedy - They have to be in separate files for reference, not only for the customer but for corporate management. I'll be more specific about how it works. I tried INDIRECT, but it's a nightmare having to open everything in order to obtain the data needed. I'm okay with VBA solutions. I've tried a few, but my VBA knowledge is preschool level. The "real data" can have an unlimited number of rows due to revolving labor and workload, but the columns are static.

peterrc - Thanks for the welcome! Can you import multiple workbooks and sum the data into a pivot table? I've tried Pivot Tables, but could not seem to get it working as needed. Also, I missed the (orders) column on the data sheets. The imports needed for the weekly report are: Name, Orders, Lines, Cases, Pallets, Hours, and PTG (Percentage To Goal).

Here is how this works:

Orders are printed out. They are logged into a daily excel spreadsheet via RF guns and given to a random amount of employees in 8 different departments. The department statistics are calculated into 1st, 2nd, and 3rd shift worksheets and saved as "Filename CurrentDate" format (aka Tracker 12.01.2019.xlsx) for daily management review.

The customer and management would like to see a weekly/monthly report included with the daily. I'm trying to automate the process.

The date-to-date is a reference to the list of workbooks that were summed.

UPDATE: I might have the data figured out using 2019 Get&Transform. Now, it's a matter of format and file selection. I might have to make separate folders for each week.

[Glenn Kennedy]

Persevere with Get & Transform. It will be FAR superior than any pre-school solution I could come up with (I'm at the same level as you, or lower!!)

[jstrem75]

Definitely! I have the data working, but I'm not sure about the weekly file pull. I think I'll have to resort to "what files are in the folder" vs "excel picking the files for me"

You can add any data to any workbook in the data folder OR add any files to the data folder, and it will update(refresh) the weekly workbook automatically.

I've attached the zip data if anyone wants to improve or comment.
---------------------------------

I wonder if I can use Harlan's Pull to get the workbook filenames in order to preselect the data per week vs using a folder.

[Glenn Kennedy]

"Pull" was one of the options I had in mind. It'll work, for sure. But it IS sooo slooow - in my hands, anyhow!!).