How to Export Specific Cell Data from Multiple Excel Files to a Master Workbook
Question details
The user needs to extract values from fixed cells and ranges across differently named inspection workbooks and consolidate them into a single master workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating inspection reports where the target data is scattered in specific individual cells rather than structured tables.
- Observed behavior
- The user requires an automated process to read dynamically changing file paths, extract fixed cell values, and append the results to the next available row in a master dataset.
Ensure all your source inspection workbooks follow the exact same template structure, meaning the target data is always located in identical cell references (like D4 or F6) across all files.
Use Power Query with Dynamic File Paths and Custom Functions
Create a Power Query that references specific cell coordinates instead of tables, and use a control table in your workbook to manage dynamic file paths.
Power Query can extract data from specific cells by treating the worksheet as a grid. To prevent the query from breaking when file names or paths change, you should avoid hard-coding the paths in the query and instead feed them dynamically from a worksheet control table.
In your master workbook, create a table containing the current file paths and file names of your inspection workbooks. Select the table, go to the 'Data' tab, and click 'From Table/Range' to load it into Power Query.
Go to 'Data' > 'Get Data' > 'From File' > 'From Workbook' and import one typical inspection file. In the Power Query Editor, navigate the imported grid to locate your specific cells (e.g., Row 3, Column 4 for D4). Filter and keep only the necessary cell values, then convert this query into a Custom Function via the Advanced Editor.
Return to your Control Table query in the Power Query Editor. Go to 'Add Column' > 'Invoke Custom Function'. Select the function you just created and pass the file path and file name columns as the parameters.
Click the expand icon on the newly created column to reveal the extracted cell data from each file. Click 'Close & Load To...' and choose to append this data into your master workbook sheet.
Experience Seamless Data Management with WPS Office
While complex Power Query M-code setups require Microsoft Excel, WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy familiar spreadsheet tools, excellent format compatibility, and easy-to-use data consolidation features without the heavy subscription costs.
Frequently Asked Questions
Can Power Query extract data if the source files are not formatted as tables?
Yes. Power Query imports the entire worksheet as a grid. You can filter rows and select specific columns to isolate and extract individual cell values like D4 or F6, even if they aren't part of an official Excel Table.
How do I handle file paths that change frequently?
Instead of hard-coding the file path in the query's M-code, create a control table in your master workbook containing the current file paths. Reference this table in Power Query to pass the file paths dynamically into your extraction function.
Will the master workbook automatically update when new inspection files are added?
Once your dynamic query is set up, you simply need to add the new file paths to your control table and click 'Refresh All' in the Data tab. Power Query will automatically process the new files and append the data to the next available row.
