In another topic, I had asked how to get specific cell reference from multiple sheets in same workbook and it is working for me now. Now I would like to ask a similar question but instead of within the same workbook, how do I get specific cell reference from multiple sheets from a different Workbook (file).
eg.
- I have WorkBook1 (WB1) and WorkBook2 (WB2) with multiple sheets with same data structure in each sheet.
- WB1 is the master file
- I need to get specific Cell reference from the sheets in WB2 into WB1.
Can we still use MATCH & INDIRECT and INDEX? If so, how? I can't seem to get it.
Thank you and hope you are able to assist.
Hello,
The best and probably easiest solution is to use Power Query by loading the data from files in a folder.
https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
Br,
Anders
Hi,
This doesn't help as each file to extract data from may have >100 Worksheets.
I need to setup 1 master file and would like to extract data from multiple files with multiple worksheets.
Example:
Master file to obtain cell Reference from:
- FileABC2000.xlsx
- Sheet1 = CellA1
- Sheet2 = CellA1
- Sheet3 = CellA1
- Sheet... = CellA1
- FileABC2001.xlsx
- Sheet1 = CellA1
- Sheet2 = CellA1
- Sheet3 = CellA1
- Sheet... = CellA1
- FileABC20xx.xlsx
- Sheet1 = CellA1
- Sheet2 = CellA1
- Sheet3 = CellA1
- Sheet... = CellA1
I hope the above is in order and hope that someone is able to assist.
Thanks
Hello,
There are of course different techniques to handle data import to Power Query, which I still regard as the best solution for you.
Import Multiple Files Containing Multiple Sheets with Power Query • My Online Training Hub
Br,
Anders
