justinrswa
New Member
- Joined
- Mar 16, 2018
- Messages
- 1
Hi there.
SCOPE:
I have automatically generated reports that need to have 5 specific cells extracted from hundreds of workbooks, in multiple different folders. All values must be formatted as one row with the source information included, such as filepath and filename, into one master list.
SPECIFICATIONS:
Every workbook contains 5 sheets. They are named "Boulder, Brookfield, Elmwood, North, South".
Each sheet looks exactly the same and contain 8 columns and 42 rows. Here is a screenshot of what the table looks like: https://prnt.sc/is5l8j
These workbooks are archived in file folders found in their respective months. For an entire year worth of data, I have "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec" as file folders.
"Jan" for example, has anywhere between 20 to 35 individual workbooks (reports). See screenshot: https://prnt.sc/is5qv6
The values in the cell range to be extracted is A6:H8. But I need the cells to be reformatted and inserted into a "Master" list.
The new columns should be: Filepath, Filename, TimeHigh, ResidualHigh, TimeLow, ResidualLow, AverageResidual. (The times are in military time.)
---
This is a close as I can find to what I need: https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet
I tried researching Power Queries but I could never figure out how to extract the specific cells.
I hope I've provided enough information. Thanks in advance!
-Justin
SCOPE:
I have automatically generated reports that need to have 5 specific cells extracted from hundreds of workbooks, in multiple different folders. All values must be formatted as one row with the source information included, such as filepath and filename, into one master list.
SPECIFICATIONS:
Every workbook contains 5 sheets. They are named "Boulder, Brookfield, Elmwood, North, South".
Each sheet looks exactly the same and contain 8 columns and 42 rows. Here is a screenshot of what the table looks like: https://prnt.sc/is5l8j
These workbooks are archived in file folders found in their respective months. For an entire year worth of data, I have "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec" as file folders.
"Jan" for example, has anywhere between 20 to 35 individual workbooks (reports). See screenshot: https://prnt.sc/is5qv6
The values in the cell range to be extracted is A6:H8. But I need the cells to be reformatted and inserted into a "Master" list.
The new columns should be: Filepath, Filename, TimeHigh, ResidualHigh, TimeLow, ResidualLow, AverageResidual. (The times are in military time.)
---
This is a close as I can find to what I need: https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet
I tried researching Power Queries but I could never figure out how to extract the specific cells.
I hope I've provided enough information. Thanks in advance!
-Justin
