Comments on: How to create external reference in Excel to refer to another sheet or workbook

This short tutorial explains the basics of an external reference in Excel, and shows how to reference another sheet and workbook in your formulas. Continue reading →

Comments page 2. Total comments: 202

  1. Can I use a formula to refer to a cell in a sheet rather than the cell.

    Eg. Rather than ='BSCA|P'!Q17 (which is data for todays date) when I do something like ='BSCA|P'!VAR!E18 (VARSheet being a sheet which tracks the cells for Todays date) I keep getting a 'There's an error in the formula you entered' message.

    Thanks,
    Rohan

  2. =VALUE(HYPERLINK(CONCATENATE("[H:\Quality Hub\KW ";G1;"\";"DECONT ";D1;" ACT202011700024760- 2023.xlsm]Materiale Stoc'!R2")))
    what is not good

      1. Put the CONCATENATE formula in another cell and see what the result is. That will tell if the result is correct for HYPERLINK function to use as input.

  3. I'm wrote macro, where I'm using Xlookup to link data from other Workbook on same Sharepoint location.
    It works fine, until I close both files (one from which link is made and one to which link is made). After re opening them, formula sometimes brakes (I can't figure out exactly why/when), weird symptom is that it gets worksheet refference changed or even "REF!".

    Bellow is my example row, Phrase Process Checking is exact name of tab, which is geting lost to REF! (tab still there), or change to other tab existing in the workbook

    ShtTM.Range("K2:K" & lastRowTM - 1).Formula = "=Xlookup(A2&H2&G2," & PathOnly2 & "'[" & FileName2 & "]Process Checking'!A$3:A$" & LastRowPCI & "&" & PathOnly2 & "'[" & FileName2 & "]Process Checking'!g$3:g$" & LastRowPCI & "&" & PathOnly2 & "'[" & FileName2 & "]Process Checking'!Q$3:Q$" & LastRowPCI & "," & PathOnly2 & "'[" & FileName2 & "]Process Checking'!P$3:P$" & LastRowPCI & ",0)"

    I

  4. Hi, thanks for your excellent guide and for sharing your knowledge.

    I'm trying to figure out how to create an absolute reference to a cell in a table on a separate sheet.
    As soon I try to copy the formula to the next right cell, it breaks, and the reference point to Roles[CostRateOrig] (the next right cell in the separate sheet) instead of Roles[ActivitiesRoles] (which it always should). Is it possible to make the reference absolute? The $ sign does not seem to work in this instance...

    Original formula:
    =(XLOOKUP(B26;$B$8:$B$16;E$8:E$16))*(XLOOKUP($B26;Roles[ActivitiesRoles];XLOOKUP(E$24;Roles[[#Headers];[BillRateOrig]:[BillRateRev2]];BillRateArray)))*(IF($D26="Yes";1;0))

    Broken formula:
    =(XLOOKUP($B26;$B$8:$B$16;F$8:F$16))*(XLOOKUP($B26;Roles[CostRateOrig];XLOOKUP(F$24;Roles[[#Headers];[BillRateOrig]:[BillRateRev2]];BillRateArray)))*(IF($D26="Yes";1;0))

    2. I solved it by setting up a "defined name" in the Name Manager and pinpointed the data as an array. Then it treated the data as absolute :-).

  5. Hi there - thanks for this. What I would love to do is use the value of one cell (using left function to pull the characters) to reference the sheet name.

    E.g., if A1 = DIB (2960452), and the sheet name is DIB, I want to have B1 =
    SUMPRODUCT(LEFT(A1, LEN(A1)-10)!$F$5:$F$35, (LEFT(A1, LEN(A1)-10)!$L$5:$L$35)/SUM((LEFT(A1, LEN(A1)-10)!$L$5:$L$35),

    Where (LEFT(A1, LEN(A1)-10)=DIB.

    This is so I can repeat the same across multiple sheets and rows, as the names change a lot and new tabs are being added all the time.

    Thanks!

  6. I have an error with this code,

    Can anyone help me why this #NAME? error is appearing.

    =IFEROOR(VLOOKUP($E3,'Follow Up'!A1:P1,MATCH('Daily Report'!F$2,'Follow Up'!A1:P1,0)),0)

    Follow Up and Daily Report are names of sheets.

  7. Hi, I'm trying to create an automated scheduling spreadsheet for equipment and personnel allocation. Currently I export data from SAP with our weekly schedule and paste it into Sheet1 of the workbook, that includes workorder number, description of works, location, date hours etc from columns A-I.

    In J-Q I then have variable drop down selections from a master list of equipment and personnel on Sheet2.

    On Sheet 3 I have have the master list of equipment in column B, with Monday-Friday dates in D-H. I want to make it so that when a piece of equipment is selected in the drop down box of a job for a day on Sheet1, that the job/workorder number on the row of that day, then appears on the same date on Sheet3 to show it is allocated, as Sheet3 has to be shared to others. Is this possible?

  8. Hi, I have a workbook with 30 sheet named 01, 02, 03, .... , 30
    In another workbook, I type this formula: =sum('[Workbook name]Sheet name'!Cell range)
    example: =sum('[Workbook name]01'!$E$37)

    I need to call every E37 value in sheet 01 to 30. How can I do it without typing the formula one by one every cell?
    Thanks

  9. When referencing another sheet using the =SheetName!CellLocation Method, is there a way to copy the formatting as well. I have a list of numbers with a select few of them in bold to reference their significance. I want to keep this formatting in my new sheet.

  10. Hi All,

    I am referencing a date from sheet 1 onto sheet 2. When I filter sheet 1 the reference on sheet 2 has changed to the data that has been replaced into that cell.

    Is there a way that I can get the cell on sheet 2 to follow the date I had referenced on sheet 1 even as it is filtered back and forth?

    1. Hello!
      I think it's not about filtering, but about sorting. When sorting data, Excel does not move cells, but only copies the data. I recommend copying all the values ​​to a new sheet using Paste Special - Values ​​and sorting them there. This will not break your formulas.

  11. I have a line of code that is referencing file and sheet, but I need to specify the range in R1C1 format so I can use it in a loop. However no matter how I format it, Excel doesn't like it. I'm extracting data elements from a machine controller into excel for printing and archiving. The machine controller has an array of 100 elements, each 100 data entries long. This is what I have now (that doesn't work):

    data = DDERequest(rsichan1, "Recipe_Library_STF[" + CStr(Index) + ",0],l120,C120")
    Range("[Tilt_recipe.csv]Tilt_recipe!" + Cell(Index + 1, 1), Cell(Index + 1, 120)).Value = data

    Somehow, when adding the reference for the file and worksheet doesn't allow me to specify the cell addresses in R1C1 (or at least I don't know how to specify it).

    This works....
    data = DDERequest(coil, req)
    Range(Cells(4, Index + 3), Cells(104, Index + 3)).Value = data

    Any thoughts on how to format this properly?

    1. Oh, I forgot to add, Index is a looping variable used to index through all 100 entries in the array. I inhereted this from another person who, rather than looping the lines of code that pull the data, there are 100 sections, each pulling one set of the data. So... whenever we need to make a modification, we need to modify over 1500 lines of code to get the new dataset. I'd rather tweak the 15 or so lines in the loop.

  12. I want to reference an entire workbook from an external excel file on my onedrive so that I can have one copy that is editable by several people and then another copy that is populated by the first, that a much larger group can view, but not edit. Is there a way to do this with out manually referencing every cell? When I try to drag the formula to other cells it does not change the cell references, so every cell has the same data from the A1 cell in the referenced workbook. How can I get this to copy to the rest of the cells but change the cell reference accordingly?

  13. Hi,
    I have an issue I can't find the fix for:

    My sheet1 is a summary sheet and sheet2 and beyond have data and are all formatted exactly the same. I want to use =AVERAGE on sheet1 for a range in sheet2 but am writing a dynamic formula so I can change the range I want on the sheet1 and Excel will know to find/look up the range I want on sheet2. The following formula I can get to work, but only takes an average of 2 non-adjacent cells (not a range):

    =AVERAGE((VLOOKUP($B$1,(INDIRECT($A7&"!"&"$A$4:$BB$60")),(INDIRECT($A7&"!"&"$AD$2")),FALSE)),VLOOKUP($B$2,(INDIRECT($A7&"!"&"$A$4:$BB$60")),(INDIRECT($A7&"!"&"$AD$2")),FALSE))

    where A7 is "sheet2", $B$1 is "2010" and $B$2 is "2020". This formula averages 2010 and 2020 only, not 2010 THRU 2020 like I'm trying to achieve...

    If I put a semicolon in where the comma is separating the 2 formulas, I get an error message. Basically I want to know how to write this: =AVERAGE(VlookupFormula1:VlookupFormula2)

    Any suggestions?
    Thanks!

      1. I think I figured out a formula that works using the ADDRESS/MATCH/INDIRECT functions.

        Original on sheet1:

        AVERAGE(sheet2!AD6:AD16)

        New dynamic formula that worked and got the same result:

        AVERAGE(INDIRECT($A7&"!"&ADDRESS(MATCH($B$1,INDIRECT($A$7&"!"&"$A$1:$A$200"),0),30)&":"&ADDRESS(MATCH($B$2,INDIRECT($A$7&"!"&"$A$1:$A$200"),0),30)

        where $A7 = "sheet2"
        where $B$1 = 2010
        where $B$2 = 2020

  14. Hi Alexander/Ablebits Team,

    I've searched everywhere for a clear answer to this but I can't find any and it seems like such a simple problem:

    Let's say I have 6 sheets. The first is a summary page, the other 5 are named "Monday", "Tuesday" etc thru "Friday". I've set up these 5 day-of-the-week sheets with the same exact tables, only with different values in the cells from day to day.

    In A2 on the summary page I want to write a long, complicated formula where Monday occurs several times in calculating Monday's results. (i.e. =((Monday!A1*2)+(Monday!A2*3)/LN(2))-(Monday!A4/Monday!A7) etc etc...)

    To see Tuesday's results, I want Excel to change the data in that summary sheet's formula automatically so I don't have to delete Monday and type Tuesday 10 times. I'd rather type the word Tuesday into A1 and have the complicated formula reference A1 and know to change the text of Monday into Tuesday in all the instances in the formula. Does that make sense?

    It seems excel should have this function since it would save a lot of typing, especially if I have 100 different sheets..

    Thanks!
    Charles

      1. Alexander,
        I got that to work, thank you very much! Btw my version of Excel didn't need the "'" around the sheet name. Less is more!
        Charles

        1. Hi Charles,

          Single quotes are not needed for single-word worksheet names, but for multiple-word sheet names they are required.

  15. I have a master data file with multiple sheets of data, each sheet with a different weeks sales data in it. Each sheet uses the format WEEK then week no as its name ie WEEK 1,WEEK 2, WEEK 3

    I then want to lookup from another workbook to figures in theses sheets but want to be able to easily change which Weeks sales data it's looking up to

    Is there a way to use a number in a cell to change the worksheet the formula looks up to

    Ie type 1 into cell A1, and the formula will look up to the WEEK 1 sheet, change the cell to a 23 and the formula will change to look up to WEEK 23 sheet

  16. Hi,

    I'm using the latest Excel version via Office365, so I can use data ranges converted to tables.
    My first workbook contains several sheets, each with a specific table. Data is referenced via the x.lookup function to table fields instead of row/col ranges. Works great !
    This workbook is used as a data container and is read-only (can only be edited by myself).

    A second workbook is pulling data from the first one , also by using the x.lookup function and extracting data from the different sheets based on specific parameters.

    Both workbooks are stored on OneDrive in the same folder.

    It all works fine as long as the first workbook (the data container) is opened on my PC. When I close this file, The second workbook gives error messages as result for the x.lookup referring to the tables. It seems that the references in the x.lookup function are not recognized any more.

    Is this normal behaviour ? Can I extract data from (dynamic) tables in other workbooks when these files are closed ? I hope this can be done without the use of PowerQuery (which is also a great tool).

    Looking forward to your reply.

    1. Hello!
      You can extract data from a closed workbook with a VBA macro, ActiveX Data Objects, or with Power Query. You can't do this with a regular reference.

  17. Nice article but what if i have worksheet named "Index", "TEST01" and "TEST02". In Index Worksheet in column A i have the values A1=TEST01, A2=TEST02. Now against B1 under Index sheet i want to capture the data from TEST01!A1 but instead of giving the direct formula i want Index.B1 to refer to the value present in index.A1 and then fetch the data.

  18. Hello, I have an excel workbook(1) that references sheets and cells in an external workbook(2), this works fine. My question is how can I easily redirect workbook(1) to the same cells but in a different external workbook?
    So I want to be able to reference the same sheets and cells, but just in a different workbook without having to manually change the references. I want changes in workbook(2) to be reflected in workbook(1).
    Hope this makes sense.

    Paul

  19. Just finished an article by Svetlana Cheusheva - "How to create external reference in Excel to refer to another sheet or workbook'. I think it is one best I have read in a few months / years. However, I have ONE major complete for both her and your organization. I have spent several minutes n her's and your website(s) attempting to find where i can register for your daily, weekly, or monthly Excel article / messages / life updates. Have yet found where that form is located. A few of your competitors maintain such a thing & I would appreciate the ability to read more of her and / or your entity's emails. Thoughts Please advise

    1. Hi Gregg,

      Thank you so much for your wonderful feedback! At the moment, we don't have such a form, sorry. I will talk to our tech guys and we'll implement a subscription to our weekly digest as soon as we can. Thank you for pointing out that omission!

  20. Hi,

    I want to use external references from one workbook (source) to another (destination). I want to share the destination workbook with my colleagues. I do not want to share the source workbook with them.

    My question: Does someone need access to the source workbook to see the data that is externally referenced in the destination workbook? Or is access to the destination workbook enough?

    Thanks.

  21. Hi, I am needing to pull data from one tab (or worksheet) titled Training Records into another tab to create statistics from. I am needing to nest 3 conditions. (If cell = assigned, then appear as Incomplete. If cell = a date 9-Dec-21, then appear as complete. If cell = not required, then appear as NR.)

    This is the formula that I have with a #NAME? error. I am using Microsoft 365.

    =IFS('Training Records'!J2=assigned,Incomplete,'Training Records'!J2=DATE,Complete,'Training Records'!J2=not required,NR)

    1. Hello!
      Always use text values with double-quotes. Use the DATE function to specify the date.

      =IFS(J2="assigned","Incomplete",J2=DATE(2021,12,9),"Complete",J2="not required","NR")

      I hope my advice will help you solve your task.

      1. Your suggestion worked. This is the formula that I have now.

        =IFS('Training Records'!J3="assigned","Incomplete",'Training Records'!J3=DATE(2021,1,1),"Complete",'Training Records'!J3="not required","NR")

        My last remaining question is how to configure the DATE formula (2021,1,1) to represent any date. Not just a specific date. Is the format listed above correct?

        Thanks, Again!

    2. I also tried this formula. I know I am so close.

      =IFS('Training Records'!J2=assigned,Incomplete,'Training Records'!J2=DATE,Complete,'Training Records'!J2=not required,NR)

  22. Hi,
    I have a sheet with Construction project details. On the other summary sheet, I need to select the project and few relevant date of selected project should appear. How can i do that? can you please help.
    Thanks

  23. Hello Svetlana!

    Suggestion -
    In the section "External reference to an open workbook", add a troubleshooting note, that if selecting a range in the referenced file is not automatically generating the reference in the formula, then perhaps the excel files are open in different instances of excel. To confirm, open Task Manager, and see if all open Excel files are nested under a single instance of Excel. If not, close one file and open it again from the other file.

    I just ran into this issue today. Found the solution here: https://answers.microsoft.com/en-us/msoffice/forum/all/cant-reference-a-cell-in-another-file/ce05b277-5b67-4fdf-ab8b-e63a1e7a610d

    Thank you for your thorough educational articles. They have been an oasis for me over the years.

    Regards,
    - Sergey

    1. Hi Sergey,

      Thank you so much for this useful information and for your kind words! The tip about different instances is added. Thank you :)

  24. Hello,

    I have tried the reference to a range of cells but that didn’t work:
    =sheet1!a1:b8 on sheet2 in cell a1 results in only A1 from Sheet1, not the range.
    I would like to have all referenced data A1:B8 from sheet1.

    What’s going wrong, i am using excel 2010.

    A1:B8 is a table, is it posible to reference to a whole table?

    1. Solved.
      Doesn’t work in excel 2010.
      Tried 365 ‘21 there it works: awesome!

  25. I have a master workbook saved on my desktop that pulls totals from several workbooks that are on my companies sharedrive. Several people work within the workbooks being referenced and they often need to add rows within the worksheets. This unfortunately is messing up the reference every time. I have removed the "$" so they should be relative refences. It does not appear to be happening when I have the master workbook opened at the time of the row addition. I am not sure what I am missing. Below is an example of what the reference looks like. F48 is where the total sits when I create the sheet. When a row is added it still pulls from F48 although the total is now in F49.

    ='\B\2020\Timesheets\project\[07-2020.xlsx]NAME'!F48

    Do you need more information?

    Thanks!

    1. Hello!
      Links are automatically changed only within the current workbook. Links to external files are not automatically changed.

  26. IF functions between workbooks

    I wish to use the IF function by referencing cells in an external workbook. I have one cell in an external workbook that is to be used as the true or false test, and if true, I want to pull the contents of another cell in the same external workbook through to my current workbook.

    I have used the link to the cell in the external workbook with the formula in the current workbook and it is not returning the result I want. The formula is

    =IF('[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$K$9="Open",'[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$C$9,"")

    Can anyone identify what is wrong?
    Thanks
    Chris

    1. Hello!
      I don't have your files. Therefore, I cannot check the work of the formula. You have not written what exactly does not work.
      For a formula with external references to work, the external workbook must be open.

  27. some diferente problem.
    Have about 10 workbooks that i need to refer, Each workbook has the same range like .=

    =MÉDIA('D:\Users\local\[name1.xlsx]name1'!$GP$4:$XFD$4)
    =MÉDIA('D:\Users\local\[name2.xlsx]name2'!$GP$4:$XFD$4)

    The range change every day (so tomorrow my range will be GQ$4:$XFD$4) for each workbookl

    How can I change the range GP$4:$XFD$4 to GQ$4:$XFD$4 for the multiples cells. I mean, how can I change only one istead 10?

      1. And thats something I never tried. And Works fine

        Thanks

  28. Hey Seniors
    I am facing the issue of Hyperlink or link. When I shared my files to another pc the link is not working?
    Is there is any solution to one workbook hyper link to another workbook sheets?

  29. having troubles with cell reference's. Referencing a different sheet with ='Wine Data'!A6. no matter what
    formatting I apply to either cell all I get is the formula displaying. Other cell references are working fine until I copy the formula and paste into another cell. Then the cell I copied will display the formula instead of the referenced cell.

  30. Hello,
    I'm trying to reference another workbook cell within box
    Is this possible?

    Here is what I'm trying to do.
    ='[https://app.box.com/s/4fcfdd2b25lyps2p46khzydj6maoejvv]Sheet1'!A1

    Is it not possible to reference other workbooks from within box? Does it have to be a local file for this work?

    Much appreciated!

      1. Hi Alexander,
        Would you happen to know if my question is possible to accomplish?

        1. Hi,
          The formula

          =HYPERLINK("[https://example.com/report/file.xls]Sheet1!A10", D1)

          retrieves the value from the cell A10 on Sheet1 from a file on the Internet. Hope this is what you need. Study carefully the articles I have recommended.

          1. Hi Alexander,
            So I went ahead and tried this formula you mentioned above. Just to test to see if it works, I set up the files as follows.

            Spreadsheet File1 = I entered the formula you mentioned
            Spreadsheet File2 = I entered a value -->50 in Sheet1 CellA10

            Upon completing this, it still does not work.
            The value I get per your formula = 0

            Any idea what the issue might be?

            Just to test to see if your

            1. Just out of curiosity.
              Have you tried this yourself in Box? or are you trying this on a different platform?
              I'm wondering if this feature is not supported in Box.

      2. Thanks for the reply Alexander,
        however this doesn't look like what i'm trying to do. Youre example & articles indicate me doing a Hyperlink to the other file on the internet.

        I'm trying to only pull the value of a cell on another workbook on the internet. Not a hyperlink to the other file.
        For example)
        1. Workbook A - I have a main dashboard tracking multiple tasks/projects
        2. Workbook B,C,D,E,F - Each project has its own separate file on the internet (in box.com)
        3. Anyone can update Workbook B,C,D,E,F as they work on stuff and the % of completion will show up on the top in sheet1 cell A1
        4. For Workbook A - I want to be able to pull the value in each of the other workbooks Sheet1 CellA1 value, so it gives me a dashboard % of all other workbooks at once instead of me having to go open all the other files one at a time.

        Hopefully this example makes sense.

        Any suggestions will be greatly appreciated!

  31. I have a MASTER spreadsheet that uses cell references to pull data from three data template workbooks. All four of these files reside in the same folder on Dropbox. Periodically, I save a copy of each data template onto my Mac laptop and I add the day's date to the name. Unexpectedly, the MASTER spreadsheet unilaterally changes the cell references to the name of one of my data backup files, replacing the original cell reference that I had created within the MASTER. So then, that group of data is no longer being pulled from the Dropbox file. Why is this happening, and how do I prevent it?

    1. Hello!
      I think you created a copy of the file using Excel - Save as ... At this time you had your MASTER file open. When you save a file under a new name, all links to it are automatically changed.

  32. I have say 6 tabs in a excel workbook. the 6th tab shows the summary of the first 5 tabs total column, which is in a common cell say A10 in all 5 tabs. How do I now show the auto-generated summary of this total column cell A10 in 6th tab with having mentioned the name of all 5 tab names?

  33. Is there a way to keep the file path to always read from the current folder that it is in?

    For example say I have the excel file in file path \\trial\test1 and its reading info from the same path. Is there a way of copy and pasting that excel sheet into path \\trial\test2 and then reading from within the new folder ( \\trial\test2 ) without having to redo all the links?

    Thanks in advance

      1. Okay no problem, thankyou for your quick reply

  34. hi my formula is
    Here is the formula is my excel named: EXCEL-ONE
    ='[EXCEL-TWO.xlsm]Sheet1'!$B3

    its looking up my second excel named: EXCEL-TWO,
    in the Sheet1, and B3 cell

    and it works flawlessly :D

    my question:
    i would like the Sheet1 word to come from a cell in EXCEL1
    so i would have a blank cell in EXCEL1, that you could type in :
    Sheet1, or Sheet2 or Sheet3 .... giving you knew the name of the tabs in SHEET2 obvisouly,

    is that possible ?

    1. Hello!
      You can learn more about creating an Excel dynamic reference to another workbook with INDIRECT function in Excel in this article on our blog.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  35. I want a copy of my excel workbook at other location and if i change one book that can reflect automatically other workbook can this happen please help

    1. Hi Priyanka,

      For this, you need to link every cell of the copy to the corresponding cell in the original workbook. It can be done with a formula like this:

      =[Book1]Sheet1!$A$1

      Where Book1 is the original workbook.

      The detailed steps to make such a reference are described in How to refer to another workbook.

  36. We have an Excel Workbook with 10 to 15 worksheets and all these worksheets interacts with one another for calculations and for data. Some of these worksheets gets Data from other excel workbooks by a cell reference formula. The main workbook depends on worksheets and other excel files/workbooks to get data and do calculations this work is done once a year. So many references, formulas and calculation are involved. Each year we copy all the last year workbooks and update the source files to do the calculations for the present year. The master excel workbook mostly does the calculation by itself when the source data feeds into the workbook and there are some excel cells in the master workbook that needs data from user but this is rare.

    Is there a way to track all the changes happened in the source files that got feed into master workbook, like old values in source values that got updated to new values and also capture old and new value in master book for comparison. Tracking should capture values not formulas.

      1. Hello, thank you for quick response. I tried track changes within excel but this doesn't work in my situation. If i have an empty cell in master excel and if i have a formula that gets value from other excel file track change shows old value as 0 and new value as formula.But if i update source file with different value track change wont capture this because formula will be same. We have some where around 12 source excel files that we update value and this data will feed into master excel file

        I am new to last changed cell concept but i want to track each and every cell that got updated in source file with old value and new value. This data will feed into master workbooks and again i want to capture old and new value so we can just manually check the two log sheets to make sure all the data is correct and feed properly into master excel file. I am hoping there will be way and thanks for future response.

  37. I have a workbook with a consolidated sheet and a sheet for each person. The consolidated sheet is more like a summary of each month and the total amount spent etc. the consolidated sheet pulls the totals, the name and the amounts form the sheets, but I wanted the sheets to pull the FTE and budget amount from the Consolidated sheet, this way when having to update the amount from year to year it can be updated in one place and not have to click on every sheet to update. The problem I am running into is when I try to sort the Consolidated sheet. I have used the =sheetname!A5 formula to pull the information onto each individual sheet but when I sort the consolidated sheet the cell stays the same even when the name moves. How do I get it to move with the sort?

    1. Hello Heather!
      If Excel uses an external reference to another file, it tries to recalculate it to get accurate final data. If Excel can’t do this for some reason, it doesn’t consider the file version as final and doesn’t get anything from it.

      Thus, if any additional actions are required from a user to recalculate the data in the source file, you won’t get the data from this file until the user opens it and allows to update the data or run macros. Most likely, the file from which you pull the data contains some external references as well. Unless the file is open, the references can’t be updated. Therefore, it is not possible to automatically recalculate the data. Besides, there may be macros in the source file that also require an additional permit to be run. While Excel is waiting for this action to take place, it doesn’t transfer the data from this file to other files.

      It happens when the source file is not open in Excel. If you have opened the workbook which you refer to, there shouldn’t be any problems and the data from that file can be used in your table.

  38. HI
    I have a few issue's with my formula below retrieving information from another sheet.

    {=INDEX('NSW RESULTS'!$B$6:$B$100000,MATCH(1,INDEX(('NSW RESULTS'!$A$6:$A$100000=$A$6)*('NSW RESULTS'!$B$6:$B$100000=MAX(IF('NSW RESULTS'!$A$6:$A$100000=A6,'NSW RESULTS'!$B$6:$B$100000))),0),0))}

    1. This didn't return max (latest) date?
    2. How to return second latest date?
    3. How to place a second sheet? Both sheets are column matched so it's cheeking same information difference years. =INDEX('NSW RESULTS'!'NSW 19'!$B$6:$B$100000,MATCH(1,INDEX(( ????

    Regards

    Tony

    1. Hi Alexander
      Have sent information through, hope you can help.

      Regards

      Tony

  39. iF I WANT TO GET A DATA FROM ANOTHER SHEET IN ONE COLUMN AND I WILL USE IT IN 6 DIFFERENT COLUMNS.
    iT WILL BE POSSIBLE TO FIXED THE DATA IN 1ST,2ND,3RD COLUMN WHEN I CHANGED THE DATA FROM REFERENCE COLUMN?
    I HOPE MY QUESTION EXPLAINED UNDERSTANDABLE.
    THANKS IN ADVANCE
    FROM THE PHILIPPINES

    1. Hello Ronnie,

      You may always disable the auto-update for your external links. Just go to Excel Options -> Advanced -> When calculating this workbook and uncheck the necessary options there.
      However, when you update your external links manually, they all get updated anyway. If you want to set a particular part of your external links, simply replace them with values using Copy - Paste Special - Values.

  40. Dear Team,
    I have some multiple excel files in folder. And i get data from this multiple files particular cell. Remember that i thought i can't open any close multiple files and receive data in one excel sheet.
    At least particular cell value in data will get in close file to open file

    1. Hello!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  41. I would like to use a cell value (1, 2, 3, etc) in the source sheet as a (looked-up) reference to another tab/worksheet (the target) in the same workbook (also named 1, 2, 3, etc), and thereby extract a value from a different cell in the target worksheet. I can't find any way of using the cell value in the source sheet to identify the relevant target tab in an EQUALS function such as =Sheet1!A2 (where the 1 is picked up from a cell in the source sheet). It seems straightforward but I've been at it over 8 hours....

  42. How can I use a wildcard to reference a file name that changes?

  43. I tried to use using Data Validation(list) Worksheet1 to go to Worksheet2 and Worksheet3 but I can not, please tell how to go about.

    1. Hello John!
      Data Validation (list) is used to populate cells with values. To move to another cell or another sheet you need to use VBA

  44. I tried a =countif(source path, wb, sh, column, value) and it only works if i open the source file. How can I make it work without opening the source file.

    1. Hello Rode!
      If your source file doesn’t contain any external references or macros, you can get the data from the file even if it is not open.
      Please check if you have the "Update links to other documents" option enabled in Options -> Advanced. When you open your file, you should get a message asking to update external references.

  45. Hi,
    I wish to pull a Title from another tab, the title refers to a Number, that i put in automatic, next to the title cell.
    No 2436(manual input -> Title (automatic pulled) - so the title cell needs to look in another tab, for the Number and then grab the title from cell next to it... understandable? :)

    THANKS :)

  46. I would like to be able to set this up:
    If C2=F then C3=8.
    I have this information listed in another sheet:
    F 8 4
    So I could just pull it from those cells. If C2=F then C3=8 and C4=4.
    The problem I am running into is that I would like to have multiple options. For example: If I enter F into that cell, I would like 8 and 4 to show up. But if I enter R into that cell, I would like 6 and 3 to show up.

    I hope I made that clear and that someone could assist me. Thank you in advance!

    1. Hello Rosalie!
      Hello
      If I understand your task correctly, the following formulas should work for you:
      in C3:
      =IF($C$2="F", Sheet2!C3, IF($C$2="R",6,""))
      in C4:
      =IF($C$2="F", Sheet2!C4, IF($C$2="R",3,""))
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  47. I have 18 subgrantees (clients) and on one of my workbooks each client has their own tab/worksheet. Each of their worksheets have the same structure, and many of the cells populate from different workbooks on our network. Occasionally I want to send one of them a copy of their spreadsheet, but I have to send it as a .pdf because when I send their worksheet out of my network, the references all get lost.

    Is there a way to copy a workbook and make whatever values are in a cell stay as they are, not as a reference?

    1. Hello!
      You can substitute all formulas and references with values in your table. Please read these instructions on our blog: https://www.ablebits.com/office-addins-blog/excel-paste-special-shortcuts-features/#copy-values-only

      You can also use our Ultimate Suite for Excel. You can select all cells with formulas in a sheet by using Select by Value -> Select Special Cells. Then you can substitute all the selected formulas with their values by using Convert Formulas to Values. Your references to other files and tables will be substituted with values as well.

      You can install Ultimate Suite in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
      I hope this information will be helpful to you.

  48. Hi All,

    Please assist with a question of mine. I have it set up as Svetlana described and it works BUT doesn't automatically update between sheets when data is changed. If I want the main workbook (the one pulling data from other workbooks) to have the correct/current info...EVEN AFTER EVERY OTHER WORKBOOK IS SAVED...I have to double click on the cells then click the workbook its referencing from a file explorer type window. I want the data to auto-update on the main workbook as soon as the "source workbooks" are edited and saved b/c I have MANY lines of calculations being made and I don't want to have to double click on every one everytime I open the main file just to make data current.

    Is there any way to solve this problem?

    1. Hello Brendan!
      First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.

      Hope it'll help, otherwise please do not hesitate to contact me back.

      1. Hello Brendan!
        _xlfn normally shows up if an Excel workbook contains functions that do not exist in the version of Excel you work with. For example, there is the FILTER function in OFFICE365, but it doesn’t exist in OFFICE2013.
        This function is currently available to Office 365 subscribers in the Monthly channel. It will be available to Office 365 subscribers in the Semi-Annual channel starting in July 2020.
        If there is anything else I can help you with, please let me know.

      2. Hello Brendan!
        I have looked into the recommendations from Microsoft and made an experiment with my own files. I have found out, as stated by Microsoft support service (https://support.microsoft.com/en-us/help/925893/external-links-may-be-calculated-when-you-open-a-workbook-that-was-las), if Excel uses an external reference to another file, it tries to recalculate it to get accurate final data. If Excel can’t do this for some reason, it doesn’t consider the file version as final and doesn’t get anything from it.

        Thus, if any additional actions are required from a user to recalculate the data in the source file, you won’t get the data from this file until the user opens it and allows to update the data or run macros. Most likely, the file from which you pull the data contains some external references as well. Unless the file is open, the references can’t be updated. Therefore, it is not possible to automatically recalculate the data. Besides, there may be macros in the source file that also require an additional permit to be run. While Excel is waiting for this action to take place, it doesn’t transfer the data from this file to other files.

        It happens when the source file is not open in Excel. If you have opened the workbook which you refer to, there shouldn’t be any problems and the data from that file can be used in your table.
        I hope it’ll be helpful.

  49. How do take the formula that brings over information from one worksheet to another.
    So if I have Sheet2!A1 in sheet one in C3 then in D3 I want Sheet2!A19 so on and so forth. I want to find the formula I can drag over to each column so I don't have to go into the 4 separate work books and click every month to bring over my totals to combine everything. I tried some different things but can't seem to get the wording correct to get exactly what I need done.

    1. Me too. :(

    2. That is the same issue I am having! I'd love to know how to do this..

  50. Hi,

    I use several VLOOKUP with table in another workbook.
    =VLOOKUP($E2;'C:\Users\daev\Desktop\Andreja\Rokovi\[Ispitni rokovi BAZA.xlsx]Sheet1'!$E$2:$V$4000;3;FALSE)

    everything working good, but if I want to copy this two workbook to another PC it is not possible to work.
    how can I write formulas to open workbook in folder they are saved.

    1. You need to change "C:\Users\daev\Desktop\" that is location you file you should write follow you new location in the new PC

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)