Assume a folder named Test on the desktop. In this Test folder, there is another folder named Survey reports. In the Survey report folder, there are multiple Excel files. All the Excel files have the same structure i.e. if there is an Operator Names in cell C9 of one Excel file, then in other Excel files as well, there will be the operator name in cell C9 itself.
In another workbook (say Book1.xlsx), one may want to extract data from specific cells of all Excel files lying in the Survey report folder. Therefore, in sheet1 of Book1.xlsx, one may want to show in A3:C3 data from cells C4, R9 and cell C16 of the first Excel file from the Survey report folder. Likewise, in sheet1 of Book1.xlsx, one may want to show in A4:C4 data from cell C4, R9 and cell C16 of the second Excel file from the Survey report folder and so on.
While one way is to open each file manually and then link individual cells, this is a very time-consuming process if there are many files in the Survey reports folder.
You may follow the process mentioned below to extract data from specific cells of multiple closed Excel files.
1. All files in the Survey reports folder should be closed
2. In sheet1 of Book1.xlsx, type C4, R9 and C16 in A1:C1
3. From cell D3 downwards, type names of all Excel files (along with their extension i.e. xls, xlsx) in the Survey report folder. To generate this list of Excel files names automatically, refer to the following post.
4. Download and install the Morefunc addin from here. This addin will allow us to use the INDIRECT.EXT function. For MS Excel 2007 and prior versions, one can simply download, install and use this addin. In Excel 2007, once this addin is installed, it will appear under Formulas > Morefunc. For Excel 2010, the process for installing this addin is as follows:
a. Unzip the downloaded folder and double-click on the Setup file
b. Navigate to the following folder C:\Program Files\Morefunc and copy three files - Morefunc, Morefunc11 and Morefunc12
c. Navigate to the following folder C:\Program Files\Microsoft Office\Office14\Library and paste the files there
d. Open MS Excel 2010
e. Go to Files > Options > Addins > Manage Excel Add-ins > Go
f. Check the following three boxes - Morefunc (add-in functions), Morefunc Tools and Morefunc12
g. Click on OK
h. Close MS Excel 2010 and reopen
i. Morefunc should now appear under the Formulas Tab in the Ribbon
5. In cell A3 of sheet1 of Book1.xlsx, enter the following formula and copy till C3 and downwards
You will observe that even with all Excel files in the Survey reports folder closed, the INDIRECT.EXT function will display the data from respective cells of those Excel files.
Since data is being extracted from closed Excel files via an addin, performance of the workbook will take a hit. Please try this on limited Excel files first.