Hi all,
I always thought that I was rather skilled at working with Excel, but I’ve got me a ‘problem’ that I can’t fix by myself. Looking at the topics and solutions offered on this forum I’m merely a novice compared to what some people can do. Therefore: Any help would be much appreciated! I’ve searched online at ‘power queries’ or macro’s, but I’m stuck!
Background: I am logging my sports results and combine these with my food intake. I'm quite a fanatic, running marathons, swimming long distances, etc. and therefore I'd like to be able to do some proper data analysis to track my progress and improve where possible.
Problem: I want to combine data from multiple workbooks in one sheet, but there are many variables involved.
How can I best combine the data? Any advice is greatly appreciated!!
I always thought that I was rather skilled at working with Excel, but I’ve got me a ‘problem’ that I can’t fix by myself. Looking at the topics and solutions offered on this forum I’m merely a novice compared to what some people can do. Therefore: Any help would be much appreciated! I’ve searched online at ‘power queries’ or macro’s, but I’m stuck!
Background: I am logging my sports results and combine these with my food intake. I'm quite a fanatic, running marathons, swimming long distances, etc. and therefore I'd like to be able to do some proper data analysis to track my progress and improve where possible.
Problem: I want to combine data from multiple workbooks in one sheet, but there are many variables involved.
- The workbook-names are variable and naming is tied to the current date (ie. “report 20190122”, “report 20190123”, “report 20190124”, etc).
- The workbooks are grouped per month (so folders are named January, February, March, etc) and per year (2017, 2018, 2019). The main location is: C:\Documents\Reports\. So the full path for a document would be: C:\Documents\Reports\2019\January\report 20190122.xlsm
- Each workbook has the same layout. They all have 5 sheets with the same names, but I only want to collect data from the sheets: “morning”, “noon” and “afternoon”.
- Within these sheets the relevant cells are: F30:I30, F40:I40, and F54:I54.
- I’d like to be able to get a monthly and quarterly overview of data results.
- And I’d like to be able to refresh the data at any moment (so that I have the most up to date overview at any moment).
- Destination file would be something like: "summary 2019 January", "summary 2019 February", and "summary 2019 Q1" for the first quarterly, etc.
How can I best combine the data? Any advice is greatly appreciated!!