Extract data from multiple cells of closed Excel files

{ 227 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.

Leave a Comment

Your email address will not be published.

*

  • 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

  • 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.

  • 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.

        • Hi,

          Have you checked to see if NBTEXT() function works? Also, I would need the two files – the base_data file and file in which you are using INDIRECT.EXT function. Upload them to SkyDrive and share the link of the uploaded files here.

  • 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)

    • Hi,

      What exactly do you have in cell D3 and cell A1. Also instead of putting the last double quotes after the !, put the last double quotes just before the closing bracket i.e. after $1.

      Does this work?

  • 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)?

  • 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.

  • 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

  • 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.

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

  • 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.

      • 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.

  • 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.

          • 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.

  • 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?

  • 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!

    • Hi,

      The procedure for installing the Morefunc add-in for Excel 2010 and higher versions is mentioned in step 4 of this Blog article. Please follow those steps.

      • Hello,

        I follow your steps..
        I’ve upzipped the file, then dubble click the setup and then I get the error.
        I don’t have any folders named morefuct since the setup stops before this…

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

    Thanks !

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

  • 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!

  • 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)

  • 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…

  • 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

  • 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

  • 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

      • 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

          • 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

  • 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

  • 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

  • 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…

    • To follow up, what changes are necessary to make the first two cell references active?

      =INDIRECT.EXT(“‘A36&\[$A37]A38’!”&B$10)

      • Hi,

        Try this

        =INDIRECT.EXT(“‘”&A36&”\[$”&$A37&”]”&A38&”‘!”&B$10)

        A36 has C:\Users\Ashish\Desktop\test\Survey Reports
        A37 has workbook1.xlsx
        A38 has sheet1

        Hope this helps.

        • I forgot to add that I’m puzzled by the $ character in …&”\[$”&… What’s its function? Even when I delete it, the expression bombs out.

          • Hi,

            This one works fine for me

            =INDIRECT.EXT(“‘”&A36&”[“&A37&”]”&A38&”‘!B$2″)

            A36 has C:\Users\Ashish\Desktop\test\Survey Reports\
            A37 has Book1.xlsx
            A38 has Sheet1

            Tried and tested in Excel 2013

            Hope this helps.

  • 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?

  • 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?

  • 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?

  • 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.

  • 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

  • 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.

    • Hi,

      The INDIRECT.EXT function should work even if the other workbooks are closed. If you have to open the other workbooks, then you might as well write the native INDIRECT Excel function itself.

  • 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).

  • 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.

  • 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.

  • 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!

      • 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,

          Please reply to the following:

          1. What exactly is there in cell D3 and A1?
          2. What type of error are you getting?
          3. Ensure that the file name in cell D2 has the file extension as well i.e. .xls, .xlsx etc.

  • Can you use the function INDIRECT.EXT with vlookup? Is there anyway I can lookup data from another CLOSED workbook using INDIRECT.EXT?

  • Hello Asish,

    I am stuck on step 5 because I am getting a reference error. I also tested NBTEXT and it works fine.

    This is the formula that I am using. Can you please help me figure out if something is wrong with the way I wrote it。

    =INDIRECT.EXT(“‘C:\Documents and Settings\Administrator\桌面\Mich\[“&$D3&”]Sheet1’!”&A$1)

    By the way the Chinese characters were not a problem in creating the list of excel files.

    Thank you in advance.

  • No it does not work. Is it possible that the quotations are misplaced? If not please explain why you place them where they are so I can get a better understanding and try to figure it out.

    • The quotations are placed just fine. To get the correct syntax, just link to any cell in another workbook (= and then select any cell of another workbook, say workbook2) and close both workbooks. Now just open workbook1. You will be able to see the placement sof ! and ‘ in the formula bar.

      • So I referenced the workbook directly to cell A2 of Univ1.xls and got this
        =[Univ1.xls]Sheet1!$A$2

        My formula now reads:
        =INDIRECT.EXT(“‘C:\Documents and Settings\Administrator\桌面\Mich\[Univ1.xls]Sheet1!$A$2”)

        Instead of:
        =INDIRECT.EXT(“‘C:\Documents and Settings\Administrator\桌面\Mich\[“&$D3&”]Sheet1’!”&A$1)

        Where A1 reads A2 and D3 reads Univ1.xls

        Is there anything wrong here? I’m still getting a reference error.

  • This does not work.

    The cell that directly references looks like this and does work.
    =’C:\Documents and Settings\Administrator\桌面\Mich\[Univ1.xls]Sheet1′!$A$2

    After adding the indirect.ext function and quotes around the above reference we get what we had before which does not work. Oddly enough, I can use the above reference while Univ1.xls is closed and it is able to reference the cell. Do I need indirect.ext or will I encounter a different problem later on.

  • When I change the Univ1.xls to “&$D3&” in the direct reference without the indirect.txt formula I get the reference error. This is where the problem is.

    I am also unsure as to how I am supposed to change $A$2 to to reference A1 (which reads: A2).

  • Okay thanks for the help! I really appreciate the time you put in I couldn’t have gotten this far without you

  • Hi,

    I love the potential of this additional function and I am trying to integrate this into a file for financial analysis of multiple assets. I have a FANTASTIC template for analyzing individual assets, but sometimes circumstances require the analysis of multiple assets as a group. My plan is to create a template where successive rows pull specified information from a number of different, but similarly formatted, asset specific files. All data will then be formatted within a use specific summary sheet using columns of similar data on a total of four sheets (these are segregated by use/purpose).

    So I hope to create a formula that pulls the filename of the source file from Column A (Rows 3+…) of the ‘Summary’ sheet of the summary file, but then pulls the cell reference information from the cell in Row 1 at the top of each column. By combining these two sources, I only have to input the source filename and everything should auto-fill. Using a selective “$” symbol, I believe I should be able to drag and it will fill in appropriately.

    An example:

    The summary sheet of my multi-asset summary file has Column A, Row 3+ dedicated to file names. An example file name would be: C:\Users\Kevin\Documents\PF Beta Files\Template Prototype 8.3.14

    Column B is intended to display the “Asset Name”, which is always stated in the tab named “Purchase” and in cell $A$1. In cell “B1” of the summary sheet I have included the tab and cell reference, stated as: Purchase’!$A$1.

    In combining the two references, I should retrieve the appropriate data for the summary sheet. Of course, the data in each row would have different tab and cell (or names) in successive columns to reference different data sets.

    The stumbling block is the precise syntax of the formula to combine the two different text references into one.

    I’d appreciate your thoughts on the process/mechanics and a suggested solution.

    Thanks!!!

    Kevin

    • Hi,

      Just to let you know that this add-in will only work on Excel 32 bit (not 64 bit). Try this

      1. In the path mentioned in column A, add the file extension followed by \. SO the entry would look like

      C:\Users\Kevin\Documents\PF Beta Files\Template Prototype 8.3.14.xlsx\

      2. Now enter this formula

      =INDIRECT.EXT(“‘”&$A3&B$1)

      Hope this helps.

  • Hi,
    Is there a way to speed this up by having it only update the derivative cell contents at the time the file is opened? The source cell contents never change, but the derivative workbook represents a complex set of functions that work on the data. I just don’t want my calculations constantly re-reading the source data.

  • Hi!

    I have the formula working and bringing back one value from one file, so thank you for the help with that!! But, I am also wondering if I can go a step further :

    1. Is there a way that the indirect.ext function can reference a full folder of files, not just a specific file (in case files change names)?
    2. If this is possible, can I have it sum all of the values from the specific cells in each file?

    I tried to keep this as simple as possible, please let me know if you need additional information.

    Thank you for your help!!

    • Hi,

      You are welcome. That add-in only works with Office 32 bit. I have Office 64 bit installed on my system and there I would not be able to help you with solving your problem.

  • Hi Ashish,

    Thanks for your very informative article! I’m trying to sort out a problem like this one, but still can’t seem to get it right. Hoping you could help! Here’s my data in the most simplified format:

    A1: [age14.xls] — name of my workbook
    B1: all’! –name of worksheet
    C1: ’10 yr
    D1: =vlookup(C1,indirect(“‘”&A1&B1&”A1:D100”),5,false)

    D1 returns a #REF! value here. even when i change my workbook name to “C:\Users\Cats\Desktop\[age14.xls]” to include the path name.

    If I use the concatenate function to modify my formula, I get the value I want, but ONLY if my workbook name does not include the path name “C:\Users\….”
    Would you have any idea why this is happening or how to rectify it?

    This is my formula using concatenate (which again, works perfectly if my workbook name doesn’t include the path):

    =indirect(“‘”&concatenate(A1,B1)&”A1:D100”),5,false)

    Thanks in advance sir.

    • Hi,

      You are welcome. I am glad you liked it. Your first mistake is that your column index is >4. If the lookup_array is from A:D, then column index has to be <=4. The following worked fine for me =VLOOKUP(C1,INDIRECT("'"&A1&B1&"'!A1:D100"),4,0) In cell A1, I tried both and it worked fine: 1. C:\Users\mathu_000\Desktop\[age14.xls]; and 2. [age14.xls] In cell B1, I typed all Hope this helps.

  • Hi Ashish,

    This has been great and very easy to follow, although I am still coming up with a blank cell. I have used the INDIRECT.EXT on a cell just to return simple cell data in another worksheet so test if it works which it does. When I use INDIRECT.EXT in my formula with the other worksheet closed it returns a no data. It does return data when the other workbook is open.

    This is what it looks like:

    =IF(ISERROR(INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$H$200″),SMALL(IF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″)=$A$1,ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″))),ROW(2:2))-1,2)),””,INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\

    This is meant to act like a lookup function but returning multiple rows of data. I will have several different users updating their own worksheet with multiple different pieces of equipment. Each piece of equipment has their own sheet in a different work book. I the above formula to search one of these worksheets (which is dependant on which user has that piece of equipment) for the equipment code and return all repairs done on it.

    I hope this is enough info for you to help me out.

    Thank you so much for your time and effort!

    Cheers,

    Lyle

    • Hi,

      Thank you for your kind words. I have Office 2013 64 bit installed on my system and unfortunately the Mmorefunc add-in does not support 64 bit configuration. Anyways

      1. I hope you are confirming the formula with Ctrl+Shift+Enter
      2. If you are using Excel 2007 or a higher version, try this

      IFERROR(INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$H$200″),SMALL(IF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″)=$A$1,ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″))),ROW(2:2))-1,2)),””)

      • Thank you so much for your response, Unfortunately when copying the formula in, there is a bracket or comma in the wrong spot and I can’t figure out where it is. I am unfamiliar with this formula as I found it online. I have confirmed using Ctrl+Shift+Enter.

        I tried taking off the ned part ,””) and it returns TRUE. It doesn’t make any sense to me but at least I am now returning something.

        • Ok well it seems to work when the other workbook files are open and from what I can tell COUNTIF functions don’t work unless the workbook is open. I am using COUNTIF statements within other sections of formulas that would require the same workbooks to be open as above. From the research I have done so far today it seems that it will not work.

          Thanks so much for your help. If you have anymore ideas on how to make the above formula work I would love to here it. If you would also prefer we could swap contact info and maybe we could talk about this on the big scale that it is without clouding up all these comments. I would love to talk to you more but I understand if you back out.

          Thanks again,

          Cheers

          • Hi,

            The COUNTIF() function does not work with closed workbooks – you are correct. However, the equivalent SUMPRODUCT() function does. Share your COUNTIF() formula here.

            For mutual learning and benefit, i’d prefer to communicate via this medium.

        • Hi,

          You are welcome. Confirm this formula with Ctrl+Shift+Enter

          =IFERROR(INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$H$200″),SMALL(IF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″)=$A$1,ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Repairs\[“&$F3&”]Repairs’!$B$2:$B$200″))),ROW(2:2))-1,2),””)

          • Hey,

            Ok so I have put the formula in and it still returns a blank value when the other workbooks are closed. All these formulas work when workbooks are open and I am returning what I need to accomplish which is great other than having to open over 20 files at the same time to view one. I still have a feeling that this is becoming a lost cause but as long as you are still willing to go the distance I will keep trying. I just don’t want to waste your time.

            As for the COUNTIF formula it is here below:

            =IF(COUNTIF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$E$4:$J$17”),A21),INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4:$K$17”),MIN(IF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$E$4:$J$17”)=A21,ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4:$K$17”))-ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4″))+1))),””)

            The Formula above is meant to search for an equipment code that is in cells E4:J17 and then return the value of the file name which is in cells K4:K17 once the file name is return to the master sheet of the equipment list then the IFERROR formula which you have been working on will know which file to search for the repair data.

            I hope this makes sense and I know there is a lot going on. If you need more back story I would be happy to give it to you, especially if it helps and maybe help build a new formula that makes more sense. I also am aware that a database program would be way more efficient at what I am trying to accomplish but I do not have time to learn a whole new language. I know excel enough to be able to find most answers online until now.

            Again thank you so much for you help. Cheers!

          • Hi,

            Confirm this formula via Ctrl+Shift+Enter

            =IF(SUMPRODUCT(1*(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$E$4:$J$17”)=A21))>0,your_formula)

  • I used the SUMPRODUCT and it is returning #VALUE just like the COUNTIF is. I opened the other Workbooks that they refer to and then they return values that are needed.

    The other one with the IFERROR that we were originally working on is still returning a blank value with the Workbooks closed. but once open they return the right values.

  • Ok so this isn’t good really. I found another hole in the entire system. If I have apiece of equipment that changes between different staff members the repairs change for what the other staff member has written down. Let me paint the big picture I will have 21 Staff members that each have their own equipment. If something goes wrong with that equipment then I would like to be able to see all repairs done on any given piece of equipment. Each item has there own sheet in a workbook that houses only that item but all of them that we have. For example a small cook stove. If it is with staff member bob then the cells finds bob’s repair sheet and searches for all repairs done to small cook stove 01. The problem lies that if bob has repairs written down for stove 01 and then it switched and Steve gets stove 01 then the cells only searches for the repairs on Steve’s repair sheet not Bob’s. With the below formula can I search all staff members repair sheets to return all repairs written about stove 01?

    =IF(ISERROR(INDEX(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″),SMALL(IF(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″)=$A$1,ROW(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″))),ROW(1:1))-1,2)),””,INDEX(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″),SMALL(IF(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″)=$A$1,ROW(INDIRECT.EXT(“‘C:\Users\User\Desktop\Camp Spider Web\Inventory\Cage Inventory\[“&$E$5&”]Repairs’!$B$2:$H$200″))),ROW(1:1))-1,2))

    So in this it searches a document, being a variable based on which staff member has the equipment. I need it to search all staff instead of just one variable. Is this possible?

      • Hey,

        Ok thanks so much for trying. I really appreciate the help as I have found it very difficult to get so much as a response to any of my posts. I truly respect and appreciate the online helping/advice community. Again thank you so much. I guess it’s back to the drawing board I go.

        Cheers!

      • Hi,
        I know (thanks to you, some months ago) that Morefunc add-in (and indirect.ext) do not work on office 64 bit.
        Do they work on win 7 (64 bit) and office 2013 (32 bit) ?

  • Hey,

    Ok so I did a bit of investigating and learned how to break down formulas and how they return values. Anyway I have found where the problem is. I hope that if you know where the problem is coming from then maybe you could figure it out as I have no idea how to do it. So here is the formula:

    =IF(SUMPRODUCT(1*(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$E$4:$J$17”)=A15))>0,INDEX(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4:$K$17”),MIN(IF(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$E$4:$J$17”)=A15,ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4:$K$17”))-ROW(INDIRECT.EXT(“‘C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded’!$K$4″))+1))),””)

    The problem comes in at the MIN(IF(Cage 1.xlsm…. =A15,ROW(…)-ROW(…)
    The #VALUE is at the first ROW which is trying to refer to a range K4:K17 This is where it is.

    Any Ideas?? If not sorry I thought I would try. I have changed my line of thinking for the other formula.

    Cheers

    • Hi,

      To make it easy to identify the error, write your formula with all data being on a single worksheet and then paste that formula here. Once that is working, then we will take it to the next level of using the INDIRECT.EXT

  • Hi,
    I followed all these steps and it’s work, but when I have some files which converted from TDMS extension to xlsx extension, it doesn’t work. there is error.
    could you help me please.

  • Does this turotial work for shareddrive locations at work? I was able to replicate the steps with my test folder on my C drive desktop but when I put in a shared drive folder it just gives me an empty cell no #REF! or anything.

    • Hi,

      Sorry but I will not be able to help you with this add-in any more because this add-in is not compatible with my SKU of MS Office (64 bit). This add-in only works with 32 bit.

  • Figured I’d check since it has been awhile – any luck on this working with 64 bit? Or do you know of another? I’d really like to reference docs without opening them. Thanks!

  • Your explanation of how to use INDIRECT.EXT seemed the perfect fit for what I need. I have windows 8.1 and Excel 2013. But when I downloaded Morefunc and run the execute file, it errors out. Will Morefunc work with 2013? Is there another download for 2013? Note: I have to pull 3 cells from about 50 workbooks in one folder, all files are identical and the data is on sheet 1, cells J9, D11 and J11.

    • Hi,

      Yes it will but I will not be able to help you with this because I do not use this add-in any more. I have Office 2013 64 bit installed on my system this add-in does not work with 64 bit Office.

  • I have been having trouble extracting data from multiple excel files contained in one folder. All of the files have the same template and I’m trying to return several data points from about 815 files. Some of the data I’m trying to return are numerical values and some are text strings. I have tried the following equation for the column D values in the shared Google file, but I keep getting #Ref.

    =INDIRECT.EXT(“‘C:\Users\Customer Service\Documents\Bids\[“&$A3&”]Project_Information’!”&C$7)

    Where column A contains the entire file name and C6 contains the data I’m trying to return from that excel file. For some reason, however, the equation keeps referencing the C6 cell in the workbook containing the equation instead of searching for the source file within the folder.

    I cannot share direct information on the project, but this file (shared with you) shows the reference sheet within each file and the cells that contain the data.

    Let me know if you have any other questions. Thanks for your help!

    https://docs.google.com/spreadsheets/d/1J7v8tVw3gng9id_1DKbgGILYLh7q3CWX3eWy0EcpHVE/edit#gid=0

    • Hi,

      I would not be able to help you with this anymore because I do not use this add-in now. I have Excel 64 bit installed and this add-in is not compatible with Excel 64 Bit.

  • Hi Ashish, Your tool is good. I have a question here. I need to search for a content inside 5- 6 closed excel files, but these files are in different sub folders.

    For e.g.
    1. Assume a folder named Test on the desktop containing 2 sub folders- Survey1 and Survey2
    My excel files will be present in either of these folders .
    \\Test\testfile.xlsx
    \\Test\Survey1\Survey1file.xlsx
    \\Test\ Survey2\ Survey2file.xlsx

    2. I need to search for text e.g. “Brakes” inside any of the cells of these excel files. The output should return me 2 columns , namely “Summary”, “Address”, form the input excel files. Position of these columns is not fixed in either of the files. E.g.

    “Summary” could be at cell C1 of testfile.xlsx OR E1 of Survey1file.xlsx OR B1 of Survey2file.xlsx
    Same for “Address”.

    Can your tool help me achieve the above OR can I tweak the code to get this done?
    Kindly advise

    • Hi,

      I will not be able to help you with the add-in referred to in this blog post because I no longer use the Morefunc add-in. This add-in only works with Excel 32 bit whereas I have Excel 64 bit. This can only be achieved by using VBA code.

  • Hello
    I slightly modified the formula for Indirectly reading data from closed excel book & it works

    =INDIRECT.EXT(“‘C:\Users\pbhagwat\Desktop\Test\Survey reports\[“&$D$3&”]book1’!A1″)

    A1 is the data incell A1 as you know.

    This way I don’t need to create table of rows & columns A,B1,C1 etc by using “&A1&” etc.

    However when I tried to cop down the formula the cell A1 does not change to A2 & I have to manually type the formula. What can be done to copy down or right etc. the formula

    Thanks

    pradeep

    • Hi,

      Try this formula in cell B2 and copy down. ROW()-1 will return 1. When this is copied down, then ROW()-2 will return 2 and so on.

      =INDIRECT.EXT(“‘C:\Users\pbhagwat\Desktop\Test\Survey reports\[“&$D$3&”]book1’!A”&row()-1)

      Hope this helps.

  • Your method is working for me to extract data from excel file, however I need to extract it from .CSV file. When tried, it works when file is open but does not extract data from closed .CSV file. Can it be done, help will be appreciated.

      • Ashish,

        We have used recently moved from a server environment to OneDrive for Business and we are attempting to determine if the indirect function will function in this environment.

        Can you advise if you are aware if the indirect function will function and if not, are you aware of any methods to replace the indirect functionality?

        Best regards!

  • Hi I have followed your instructions clearly and have tested that the addin works via the NBTEXT method that you prescribed to the other users.

    I tried all your instructions on my own spreadsheets and it would not work then I tried it with fresh files so that the data that was being collected was simple as you explain and I still get the #REF problem.

    If I send my files to you can you take a look and show me what I did wrong?

  • is there any way to get around the problem of file names with an apostrophe (eg ‘leo’s excel file’ not working with indirect.ext ?

    • Hi,

      I would not be able to provide you any support on this because the Morefunc add-in does not work with Excel 64 Bit. Since I have Excel 2016 64 Bit installed on my system, I will not be able to help you. Sorry about this.

  • Hi,

    I’m not sure if this point was rised, nevertheless if I’m about to extract data from Excel files which contains same structure but different sheet names I recievd error. Is there any solution but changeing names of sheet names?

    • Hi,

      I cannot provide support for this post any longer because the add-in used to solve this problem requires installation of Excel 32 bit. I have Excel 64 bit installed on my system.

  • What if the cells I need to extract are not on sheet one and/or are on a named sheet? Instead of just writing a cell reference, can I write a sheet and cell reference?

    • Hi,

      Thank you for writing in. I cannot provide support on this thread anymore because this add-in (Morefunc) requires one to install Excel 32 bit whereas I have Excel 64 bit installed. Sorry.

  • Hello,

    I need to create a series of formulas or a macro that will find the Efficiency for each line and each group of lines . The data is exported to include each stock keeping unit that has run changing the number of rows and the report sometimes has additional information the creates additional columns. So the data needed is always in a different place.

    Basically, I want to see if I can create a series of formulas or a macro that can be created to get each month of data without having to manually re-enter the data in excel 2010.

    Thank you

  • To: Ashish Mathur,

    Thanks for your instruction. Your example works. I will try it in my work.

    I test the equipment and collect hundreds of files in the same format. I want to summarize these data and show them in a graphic. I will try it in your way.

    Thanks again.

  • I have a folder with 130 excel files . I want to extract data based on their names from all the files into a separate file . i.e i want all sharma in 1 file , pandet in 1 file , etc . Please help me out .

    • Hi,

      Thank you for writing in. I will not be able to help you with a solution to your query because the Morefunc add-in only works on 32 Bit. I have Excel 64 Bit installed on my system. Sorry.

  • Hello Ashish,

    I have a weird issue I have with the excel sheet.

    I have a streaming excel sheet (let us say A) which downloads data from one of the brokers website. I have Sheet B, which is offline and a different work sheet. Now I try to extract data from Sheet A (streaming one) to Sheet B (which is offline) using vlookup for some reason this damn thing doesnt work. Yes I did see your solution where you had asked me to open one sheet and then the other one using Control+O. But sadly, I cant save Sheet A as once I save it the streaming (live) stops. I have tried using a separate sheet within the Sheet A, but for some reason the vlookup just stops working on the streaming sheets. Please help me here.

    Regards
    GK

      • Thanks much for your immediate response Ashish, much appreciated. Are you available on Team viewer, as sending files wont help as then again it becomes a saved file and vlookup works on these files. Let me know if I can post snapshots, and if that would help.

        Cheers
        GK

  • Here is the shared link, not sure how to give you access, i guess i will wait till you click on “Request Access”.

    https://drive.google.com/file/d/1mrjRo7EezuOmzvBbI5QUXHIlJl_hT8LA/view?usp=sharing
    https://drive.google.com/file/d/1u2N1a3nOYGejTj5GG18RMl3jMJvsoMrg/view?usp=sharing
    https://drive.google.com/file/d/1uabdrmoCKi3mvsDHLCHa4XUpi6o3qdPG/view?usp=sharing

    3 images in the pic.

    1. First pic just has the snapshot of the vlookup formula in the offline sheet
    2. Second pic – Table Array – Trying to choose the second sheet (Streaming one which has real time data streamed from website) – Choosing the column E&F where I am trying to get the value of “Col F”. As you can see, the column just gets highlighted, while actually we should have moving dashes to confirm the area chosen in vlookup.
    3. Pic of the table array after choosing the Col E&F – As you can see the Vlookup Table Array is empty.

    Some of the things that I tried –
    (a) Tried put the offline sheet as part of the streaming sheet – which was quite tricky for me to do, but still did it (as the offline contains data for the pivot etc). Still no go.
    (b) I created the pivot sheet within the stream workbook (in a separate sheet). Even pivots failed to work in the streaming sheet which I find it quite frustrating and strange. While the first time when I put the data and create the pivot, it worked. Later when I updated the data, and refreshed the pivot, the pivot doesnt update with the new data.

    Let me know if you need any further clarification to understand the issue faced by me.

    Regards
    GK

    • Hi,

      The problem of not being able to select the range on the second worksheet when providing a range within the second argument of the VLOOKUP() function generally arises when two instances of Excel are open in the same session. The only way i know to work around this problem is that i open the workbook (where i want to write the VLOOKUP() function) by pressing Ctrl+O when the streaming workbook is already open. You’ve mentioned that you have already tried this – so i am out of ideas here.

      Have you tried to import data from the website into the Query Editor (available under Data > Get & Transform > New Query). We will then attempt to write the VLOOKUP() in the query editor.

      It may be worth a try.

  • Thanks so much Ashish. I will give it a shot on Monday, as market is closed and no streaming and things will work as designed now when there is no data-streaming.
    Talking about the Control+O option, I had tried to open the streaming sheet through the pivot sheet, which failed because once the streaming sheet is saved, the live data stops streaming so this is of no use. Now that you mentioned, let me try to open the pivot sheet through the streaming sheet… wonder why i didnt think of this before :).

    Just checked your second option and dont see Get and Transform. Can you kindly clarify please.

    https://drive.google.com/drive/folders/1r4F9d9tLZuTFVKjc2g4H3V9Ut3uxrbpl

    Many thanks for your continued assistance, much appreciated.

    Warm Regards
    GK

  • Dear Ashish

    Today I opened the Streaming sheet first (live data update), and then Ctrl+O the pivot sheet, and voila…. things are working fine. Much thanks but I wonder how did you find such a trick, this doesnt sound logical at all :). But thank god you did.

    I have another query (not leaving you soon :)) :

    Streaming Sheet (from the website) – Say A

    So I basically extract live data by right clicking on the site (which requires login) which opens up the excel sheet and the live streaming is on.

    Here is the challenge, whenever there is a new update on the site (page), this doesnt update the streaming sheet. While the values (existing scrips) gets updated, any new scrips added to the page (in the site), doesnt get updated in the streaming sheet. Is there anything that you can think of to make this happen ?

    Thank you once again.

    Warm Regards
    GK

    • You are welcome. The reason my suggested technique works is that the second Excel file is opening up in the same instance of Excel. This allows formulas to work seamlessly. In your solution, another instance was firing up in the memory of the computer causing communication between the files to break down.

      I do not understand your second question. Which site are you downloading from? What are you downloading? What current method are you following?

    • Hi,

      For mutual learning, I’ll appreciate if we can have all communication here. If there is a file you need to share, then upload it to some service such as Google Drive/OneDrive and share the download link here.

  • I have two purchase order templates in a folder. They are both read only. One is for items bought by price per quantity. The other is for materials bought by weight. They are ‘saved as’ the next available number in order, i.e. 25001, 25002, 25003, regardless of which template is used. A separate workbook in another folder is used to document payment approval, date of approval, and invoice #s. Is there a way I can get the monthly workbook to automatically fill in the p.o. number, type, and vendor as they are saved in the folder. An example would be if i opened read only template blank p.o.’s each saved as 25004-S (Drive:\Purchase Orders\25004.xlsx) and it was for items bought from Vendor A then on “current month 2018” (Drive:\Management\Management\Purchase Orders) they would appear automatically as follows.
    column a b c d e f
    (next unused row)25004 S Vendor A
    column C D E have to be filled in manually as does G because invoices come through mail, email, and etc. but simply having these three things automatically go on the monthly would cut out a lot of data entry. Any advice or help would be greatly appreciated. Thanks.

    • Hi,

      The add-in required (Morefunc) to solve this problem only works with 32 Bit MS Excel. I have 64 bit MS Excel installed on my system so i would not be able to offer any help. Sorry.

  • Hi there, is there a way to install this add-in without running the setup.exe? I am asking because I want to install it on my work computer which does not allow random installation of .exe files. Could you provide the add-in files to copy to the add-in folder directly?

  • Hello. I really like and rely on the morefunc functionality and have been using it for years. I’ve run into a problem… perhaps it’s a system resource issue. I create successive Excel files for a number of months, each seeking information from the previous month’s Excel file. A cell in the workbook that has worked for all files from February until July of this year, suddenly returns a “#REF” error. The formula in that cell contains the “INDIRECT.EXT” function. When I search the error drop-down window for that cell it states that a cell that the INDIRECT.EXT function is relying on “contains a constant”. The cell being relied upon by the INDIRECT.EXT function is in the same worksheet. I tried opening all the workbooks from February to July as well at the same time, to troubleshoot, then I received a notification stating, “Microsoft Excel is waiting for another application to complete an OLE action.” Is there a potential limit to the number of files that rely on each other that may be causing this error? Any input would be appreciated!

  • Hi Ashish

    I tried below formula which is returning an #Ref! error.

    =INDIRECT.EXT(‘D:\Forte\DSR\[Sep[B2.xls]Sep-18]Sep-18’!H7)

    couldn’t understand the problem. Is there any error with the quotation marks?

    Thanks in advance

  • Hi Ashish,

    Looking though this thread, it appears that the Morefunc add-in is no longer compatible for newer versions of Excel. Do you have any other add-ins that will run on Office 2016 x86 bit that will achieve the same function as this add in?

    Thanks

  • I am trying the INDIRECT.EXT function and getting a #REF error also. I am sure it is because of the version of excel i am running – excel 2016 32 bit version.