Extract data from multiple cells of closed Excel files

{221 Comments}

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

=INDIRECT.EXT("'C:\Users\Ashish\Desktop\test\Survey Reports\["&$D3&"]Sheet1'!"&A$1)

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.

RSS 221 Comments…

 Share your views
  1. Hi,

    Could this formula be adapted to pull data the meets a certain criteria?

    I need to collate data from 5 spreadsheets into one. The data is in the same place for all sheets but I only need those cells that match a criteria (for example, TBA in cell D)

    How would I rewrite this formula, assuming this is possible?

    Thank you in advance

  2. Hi there,

    unfortunately I am unable to upload a copy of the spreadsheet in question, however I have linked an example spreadsheet which gives similar information:

    https://docs.google.com/spreadsheet/ccc?key=0AixTNMke635EdGFoUTA4VVlrSTBOU0FrVWxFeVotWVE#gid=0

    Basically what I want to do is extract the information for "tba's" into a new spreadsheet, including name address, etc.

    This would be across multiple spreadsheets, listed in the same fashion

    Thank you for any help

    • Hi,

      Getting data from the same tab name of five files into one based on condition that there must be a specific value in column C will make the consolidated file very slow. In this case, it will be better to consolidate data from multiple workbooks into one and then apply a filter on column C.

  3. I am trying to use your method of extraction above and I am getting an error when trying to pull the data(Step 5), the error is #REF! Suggestion on what I could be doing wrong?

    • Hi,

      Check for two things:

      1. Has the Excel add-in been installed correctly. Enter 123 in cell A1. In cell B1, enter =NBTEXT(A1). Do you see the number being spelled out correctly in cell B1? If not, then there is some installation problem - reinstall the Excel add-in
      2. Ensure that you enter straight quotes in the INDIRECT.EXT formula and not curly quotes

      If 2 above is not the reason, then please share your files.

  4. Hi Ashish,

    I am using the method that you provided here to consolidate data from A.xlsx and B.xlsx. I have three colums A, B and C in each of these files and they both have one row of data. File A has Jay, 5, 7 and File B has Smith, 2, 3. I want to consolidate these two and see both the rows in one file book1.xlsx. I have used the following formula and I am getting value '0' in all the columns. WHat am I doing wrong? I tried NBTEXT and it is displaying the text correctly.
    =INDIRECT.EXT("'C:\Users\jay.koduri\Desktop\Test\Survey\["&D3&"]Sheet1'!"&A$1)

  5. Hi, very many thanx for the morefunc set and explanation on how to install it.

    Working great on excel win32 systems.

    I was using indirect.ext but i see there is many others included. Cant imagine what i couldve done if indirect.ext dint exist. wonder why indirect doest work like indirect.ext or any other function that retrieves data from closed workbooks.

    Anyway i am writing to ask if you could suggest some modification to the code which will make it work in win64bit PCs. morefunc.xll is not being considered valid!

    Thx again.

    • Hi,

      You are welcome. Glad to hear that it is working well. I do not quite know why it is not working for Windows 64 Bit. Have you followed the steps mentioned for MS Excel 2007 and higher versions (Step 4)?

  6. Hi,

    I've got the same issue as "kt_027", working on Win7 64bit and Office2010 32bit. I just get a #REF error although if I'm evaluating the formula it looks ok.

    It drives me nuts. Could you test it on a Win7 64bit machine?

    Cheers,
    Mike

    • Hi,

      I do not have a Windows 7 64 Bit machine to test it on. To test whether the add in has been correctly installed or not, try this

      1. In cell A1, enter 234
      2. In cell A2, enter =NBTEXT(A1)

      Cell A2 should spell the number. Is that happening. If it is, then the add in has been installed successfully and therefore there could be a mistake in your formula.

      Paste your formula here.

  7. Hey,

    Truly the great add-in for Excel, but when installed and enabled in Excel 2013, it prevents the newest Quick Analysis tool from working properly - charts part just doesn't show... Maybe you know how to resolve this?..

    Thanks in advance,

    Alex

  8. Hi, very many thanx for the morefunc set and explanation on how to install it.

    I worked allot with morfunc.
    i replaced machine and installed win 7 64 bit and office 2010 64 bit and morefunc didn't work.
    I replaced office 2010 from 64 bit to 32 bit (still win 7 64 bit remained) and followed your installation suggestions and now it works !!
    thanks.

  9. if there are differene spreadsheet from where the data needs to be extracted then how can it be done