Extract data from multiple cells of closed Excel files

{117 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 117 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

  10. When I put the indirect.ext function into the vlookup function I getting a #REF! when the workbook I am linking to is closed. When I have it open it works fine.

    • Hi,

      Please share your formula.

      • VLOOKUP($A$3,INDIRECT.EXT("'C:\Path\[Annual_Sales_2013.xls]"&$A$5&"_Sales'!$A$1:$BJ$65536"),MATCH(VALUE(RIGHT(B$6,4)),[Annual_Sales_2013.xls]January_Sales!$9:$9),FALSE)

        • Hi,

          I cannot identify any mistake in the formula. One thing you can try is to give a smaller range reference i.e. Sales'!$A$1:$BJ$5000 instead of Sales'!$A$1:$BJ$65536 and January_Sales!$A9:$J9 instead of January_Sales!$9:$9.

          If this does not work, then I suspect that the Morefunc add-in has some installation problem. To test for that, try this

          1. Type 12 in cell A1
          2. In cell B1, enter =NBTEXT(A1).

          Do you see the result as Twelve?

          • Hi,

            I tried this formula and it worked absolutely fine even when I close the Base_data file

            =VLOOKUP($B6,INDIRECT.EXT("'C:\Users\Ashish\Desktop\[Base_data.xlsx]Data_"&$E$1&"'!$B$3:$E$9"),MATCH(VALUE(RIGHT(C$5,4)),INDIRECT.EXT("'C:\Users\Ashish\Desktop\[Base_data.xlsx]Data_"&$E$1&"'!$B$3:$E$3"),0),0)

            However, when I increase the range to B3:E10000, then I get a #REF error. So there seems to be some internal limit on the range height which this function can handle.

  11. Ashish,
    I am having similar problems to other individuals where the INDIRECT.EXT is returning a #REF error when the source file is closed. I have tried the NBTEXT() function, and it returns the proper value.

    Context:
    A2: Filename
    A1: Sheetname
    A3:O3 : "A"-"O"
    Q5:Q25 : "5"-"25"

    Essentially, I am making a copy (needs to be separate) of sheet on an existing document, with cells corresponding to their partner cells.
    Syntax: O6=INDIRECT.EXT("'"&$A$2&""&$A$1&"'!"&O$3&$Q6)

    Works fine when other doc is open; will not work when closed.

    • Hi,

      The following worked fine for me

      1. In cell A1, type Data_Sales - this is the sheet name of the closed MS Excel file
      2. In cell A2, type C:\Users\Ashish\Desktop\[Base_data.xlsx] - this is the path of the closed Excel file
      3. In cell A3, type D
      4. in cell A4, type 13
      5. In cell A10, the following formula gave the correct result even with the Base_data file closed.

      =INDIRECT.EXT("'"&$A$2&$A$1&"'!"&A3&A4)

      Please check your syntax.

      • Even when adjusting the syntax to match yours, I still get a # REF error. When I evaluate the formula and it reaches the reference to A2, the message displays that "the cell currently evaluated contains a constant" and will not progress any further.

        Is it possible that this add-in doesn't work with macro-enabled workbooks?
        I see that every example you use has a ".xlsx" extension, but mine is a ".xlsm".
        That is the only difference I show between your syntax and mine.

        • Hi,

          It is working fine for me even when I type C:\Users\Ashish\Desktop\[Base_data.xlsm] in cell A2.

          • After restarting the program, the function is working. Thank you for all your help!

            Ideally, I want to have the source document be sourced in an online Sharepoint document library. I have created a test document for this purpose, but the function only works now when both documents are open.

            Is there a problem with this function referencing a document housed online?

          • Hi,

            Glad to hear that it is working fine. The formula should work even when the workbook is closed - that is the very purpose of the INDIRECT.EXT function. I have never tested it on any document online.

  12. Hi

    Works fine when the files are in one folder, but how would the example formula look if you want to go through all the subfolders aswell?

  13. [...] a function , I think you may need VBA code to do that - but not certain there is a function - Extract data from multiple cells of closed Excel files but last time I tried it - it did not work on excel [...]

  14. Victor Pettersson June 27, 2013 at 3:06 pm

    When I try to run the setup.exe and click next after the "Select program folder" I get an error message that reads:
    "The name:

    is not valid.
    Please reenter it."

    this is regardless what options I enable in the installation before this.

    I run Office 2010 32bits on Win7 64bits.

    Thanks!

  15. Hi there,
    would you have the equivalent for MS Excel for MAC 2011 release 14.3.4 (version 130416) ?

    Thanks !

  16. Just a great big "thank you" for your article, which saved me hours of copying and pasting. Brilliant!

  17. Hi Ashish,
    You mention that 'performance of the workbook will take a hit'. I need to extract data from about 50 workbooks/files with about 100 cell values from each workbook/file, and wondering if this method would be suitable for that number of files without crashing my summary workbook. Do you think that would be possible?
    Thanks!

  18. Just want to say this is just awesome. Thanks for publishing Morefunc.

  19. Hi,

    I tried the following formula but turns out #REF error. I've tried NBTEXT() function and it works well. Appreciate your assistance. Thanks in advance!!

    =HLOOKUP(AG3,INDIRECT.EXT("'Path\[List Summary.xls]"&LEFT(N3,3)&" Currency'!$Q$8:$T$50"),MATCH(AB3,INDIRECT.EXT("'Path\[List Summary.xls]"&LEFT(N3,3)&" Currency'!$G$9:$G$50"),0)+1,FALSE)

  20. I waste hours setting up linked spreadsheets and copying lists of filenames. Morefunc and indirect.ext are fantastic.

    the things you did't know you didn't know that Excel could do...

  21. Keep getting #REF! on cells with indirect.ext function. NBTEXT works. Running Win8 with Excel 2000. This formula works on Excel 2000 on a Win XP machine.
    Here is the formula.

    =RIGHT(INDIRECT.EXT("'C:\1_Edmonton\INVOICES\"&$M$1&"\["&A5&".xlsx]invoice'!$A$8"),7)

    This formula is in B5
    Value in A5 = 130098
    Value in M1 - 08 August 2013

    Thanks

  22. It works with .XLS extension on Win8 and Excel 2000. Do you know of a good batch XLSX to XLS convertor that is not too expensive?

    Thanks

  23. Worked for me after a few attempts strangely the REF error dissappeared thanks Man

  24. Hi Ashish,
    I have had a problem exactly like your example ”Extract data from multiple cells of closed Excel files”. I ran through your blog first and set up the exact same example and everything worked fine. I then set up my problem in the same fashion and the file names transferred through perfectly but I kept getting a #REF error in cells I was trying to retrieve the data into.
    I think I have tracked the problem down. Your example uses Sheet1 as the worksheet you are retrieving the data from within the spreadsheet. My multiple spreadsheets have Summary as the name of the sheet and it is the entry of this in direct replacement of the Sheet1 in the formulae which is causing the error.
    I would much appreciate any ideas you have as to why this is occurring.
    Many thanks and a great blog.
    Kind regards
    Nic

    • Hi,

      In your files, if you change the name of the worksheets from Summary to sheet1, then does the solution work fine?

      • Ashish, interestingly when I change the worksheet from Summary to sheet1 it worked perfectly.

        Obviously, I changed the sheet1 in the formula to Summary in the first instance.

        Regards

        Nic

        • Hi,

          Please post the INDIRECT.EXT formula which you have written.

          • Ashish, my apologies for the delay but I had my laptop stolen and I had to strat again. The formula that I am using is

            =INDIRECT.EXT("'C:\Documents and Settings\Nic.Morgan.QS-PC\My Documents\VM Assets\Individual Sheets\["&$A5&"]Summary'!"&B$3)

            If I use the worksheet name as Summary the formula doesn't work. I then open the spreadsheet, change the worksheet to Sheet1 with a similar change to the formula it does work. Then very confusingly if I swap the Sheet1 workbook reference back to Summary on the worksheet and formula it works!

            I have a few hundred spreadsheets so I don't want to open them all up and do this.

            It is probably unrelated but there is one spreadsheet in the folder that it refuses to recognise even though I have removed and readded it to the folder.

            Thanks again

            Nic

          • Hi,

            From your description, I cant even guess why the result is wrong. Sorry, I cannot help.

  25. Well thanks for all of your help Ashish. I am still trying to resolve the obviously small issue and if I find a result I will post it.

    I have now brought it down to a simple problem. There is an error shown with the above formula but if I go into the spreadsheet that the data is being extracted from. Change the Worksheet name to anything, then change it back to Summary and save the formula works????????

    Many thanks

    Nic

  26. Dear SIr:

    Could you educate me on the proper syntax for doing a block of text that differs in cell reference points from source sheet to endpoint sheet? For example:
    I use:
    =INDIRECT.EXT("'c:\[aaa.xls]Sheet1'!C8") a single cell transfer, and I use: =INDIRECT.EXT("'c:\[aaa.xls]Sheet1'!RC",,2) for transferring data when the cell reference matches the source sheet, but if I want to use indirect.ext to copy B36:D40 from the source sheet into a cell block of A32:C36, what would the proper syntax be?

    Thank you,
    Steven

  27. Ashish,

    Sorry for the repeat question (I sent one by email), but in the OP you wrote:

    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)

    Now, what should the syntax be if all three components of the cell address:

    1. Path (C:\Users\Ashish\Desktop\test\Survey Reports\)
    2. Workbook name (here D3), and
    3. Worksheet name (here Sheet1)

    are in three separate tables in the open worksheet? I can't make it work...

  28. Thanks, but the proposed solution does not work--the double-quoted single quote gets highlighter as an error. I have tried so many quoting combinations, and none of them work (Excel 2003).

    Could someone try the suggested solution to see if's my setup/configuration, or is this an INDIRECT.EXT bug/feature?

  29. Does morefunc work with Excel 2103, the version that comes with Office 365?

  30. I need to send a lot of Excel files that I have linked using the indirect.ext with to my Accountant. I tried to include the MoreFunc add-on to one of the sheets but get an error. ”run time error ”53” file not found. how can I send him the file?

  31. My excel files are in a sharepoint library. Can this formula be used to get data from an excel file saved on a server? Example filepath:

    https://mydomain.sharepoint.com/WamDB/Master%20Project%20Workbook%20-%20Admin%20Bldg.xlsx

  32. How can I modify the formula to capture the URL if the target cell has a hyperlink? I've found the VBA code to do so, but not clear if that needs to be run on the target file or the searching file?

  33. Just noticed the question above mine from Rico. I've used the solution to target server-based files and on MS365 sharepoint using SkyDrivePro. Works great in both cases. For SkyDrivePro, the files are replicated on my hard drive, so not sure how it would work if not.

  34. Works great - thank you for saving me opening 2000+ invoice worksheets

  35. Hi Ashish,
    Thank you for your explanation on how to use Morefunc. Unfortunately I can't seem to get it to work on for me.
    I am using the following formula, similar to the one you provided :

    =INDIRECT.EXT("'C:\Users\KAN67940\Desktop\demo tools\["&$D3&"]Dynamic Tool'!"&A$1)

    where D3:D9 are the 7 files I would like to draw data from.
    In A1 I have put AM13, as that is the source cell in each file I want the data from.

    I tried moving the apostrophes around, restarting Excel, etc. What on earth am I doing wrong?
    Is it because the data I am trying to retrieve from the source cells is text? Is it because there are macros running on the source files? (I put in combo box drop-down menus).

    I sent a SkyDrive link to you with one of the source files in case you wanted to take a look at it via your 'Contact' page.

    Thank you so much in advance!

    -V

  36. Saved tons of my time. Had to struggle a bit but was able to get it right with some compromises.. like i had to keep all my other excel spreadsheets open :-) Thanks much.

  37. How do I validate data from the same sheet? I tried using DATA tab and validating, But If i re run the generated VBA code it shows an error 1004 for the INDIRECT() statement.
    Mine is code for Drop down menu wherein the output options for column B are dependent on the Input i choose in the column A("I validate "A" and use =Name).

  38. Rayapareddi Jayaram March 5, 2014 at 3:18 pm

    As per your suggestion i used Indirect.Ext coupled with vlookup function.
    There are around 20 sheets in the workbook named Sal_f16_2013_14.xls (Excel 2007). Each sheet of this workbook pertains to each of the employee.

    For Each month there is a seperate workbook with names like STAFF_SAL_04_2013.XLS, STAFF_SAL_05_2013.XLS and so on for 12 months. In this sheet the data pertaining to all the employees is available.

    To update the Sal_f16_2013_14.xls sheets instead of manually copying and pasting I used =IF(A9””,IF(A9=999, AC9,VLOOKUP(A9,INDIRECT.EXT(Main!AA6&TEXT(B9,”MM”)&”_”&TEXT(B9,”YYYY”)&”.XLS]Main’!A9:P30”),8,FALSE)),0)
    AA6 defines the file name path like ’D:\desktop 01-08-2012118usr006\SAL\SAL\BR_SAL_FILES\[STAFFSAL_
    A9 value is blank and as soon as i enter the value the related cells are being updated.
    It started working fine. but all of a sudden after data for two three sheets are obtained suddenly all the cells start showing #NAME.
    Closed the file and reopened and again after entering the values the same is repeating.
    and suddenly sometimes it is showing #REF

    Is there a way to make the target file not to refer the other worksheets all the time and can we control to get the data for a particular row only by pressing a command button and once the data is updated for a particular row
    and remain constant till next time i press the command button.

    Will be glad if you can suggest me some thing to overcome the problem

    Thanks,
    R Jayaram

    • Hi,

      I cannot figure out the problem. If it is working fine for some and not for others, it remains just as much as a mystery to me. Just check this - In cell AA6, you mention STAFFSAL_ but in the second paragraph, you mentioned STAFF_SAL. Other than that, I have nothing to suggest.

  39. I know there are lots of people with Excel 2010 having compatibility problems but I found a solution on the MSFT site (believe it or not, first actual solution I found there).

    Copy the Morefunc.xll file to the Addin Folder:
    C:\Users\xxxxx\AppData\Roaming\Microsoft\AddIns
    xxxxx = your user name
    You might need to active show hidden files and folder under tools on windows explorer.

    In Excel go to File|Options|Add-Ins

    On the bottom under Manager: Excel Add-ins press Go...

    Then Browse, then select Morefunc.xll

    Then OK

    Worked for me and made the indirect.ext function come back to life. Yay.

  40. Hi Ashish
    I am having similar problems to those above and getting #REF and I used the =NBTEXT(A1) and that worked fine.
    I am in marketing (so I don't have a strong excel background at all), I am simply trying to pull email addresses from booking forms.
    https://onedrive.live.com/?cid=7462AA645CB8B6FC&id=7462AA645CB8B6FC%21106
    This is where my documents are - it is only a sample document as the full version contains private information.
    I need the information that is in cell K15 (although this is a merged cell [K15-N15] - will that make a difference?)
    In the end I will need to pull this one line of information from over 1000 documents (I will break this down into smaller groups).
    Do you need any other information?
    Thanks so much!

    • Hi,

      Unmerge the cells first. Also, please post the exact formula which you have written.

      • I cannot actually unmerge the cells as that would involve me going into each form one by one (and there are over 1,000) and then I might as well copy over the email addresses one at a time. Does this mean that i will not be able to use your formula.
        I was using =INDIRECT.EXT("'C:\Users\Michelle.Langelaan\Desktop\booking form tester\["&$D3&"]Sheet1'!"&A$1)
        Thanks

    • Hi,

      I cannot download the file now. I get a message saying that that "This item might not exist or is no longer available". Share the file again.

Leave a Comment

Your email address will not be published.

*