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 10. Total comments: 361

  1. Hey,

    Thx for the info and detailed explanation. I have an issue which i cant solve.

    I will be creating a new budget and workpapers from 2016 to 2017. When i copy 2016 and paste it and change the name to 2017, the links of over 200 workbooks stays at on the 2016 folder.

    I have to change the links of each file from 2016 to 2017 for each workpaper one by one.Is there a shorter way to do this?

  2. Hi

    i'm a beginner,i need help, i try to connect selection from other sheet to take data for example i entering D on sheet agustus and in the other sheet i want d become "07:00" but the result is always value but when i put all the file in one sheet the result is good, here is the formula

    =IF('Agustus 16'!V11:V12="X","-",IF('Agustus 16'!V11:V12="D","07:00",IF('Agustus 16'!V11:V12="N","19:00")))

    Please Help

  3. Hi Svetlana, thanks for all the tips. Here's a new twist:

    Is there a way to reference cells of a CLOSED file in the same folder by using its name only, not the full path ?

    These files are inside a dropbox folder that is shared with team members, and the structure of dropbox means that the root elements of the path change with each individual, so the full path references don't work.

    Thanks!

  4. Hello, and thank you for the always help full posts.

    If the path for the source file in =SUM('C:\Documents\Sales.xlsx'!Jan_sales) is a named range (let's said srcpath )when entering this formula =SUM(srcpath!Jan_sales) I get the #REF error, why?

  5. As mentioned in this thread, if I create a link in workbook A to another workbook B, Excel shows only the workbook name and cell address in the link. This link is updated to include the full path when workbook B is closed. Does anyone know how/if I can get the full path of the link while workbook B is open? I need to get the full path via VBA. Cant find any properties of the cell that give the full path until the source is closed.

  6. re: Referencing between sheets in the same workbook:

    Svetlana,
    Thank you so much! You are the only source out of dozens I have read that address how to enter the sheet name if there are blank spaces in the name of the sheet (by enclosing the sheet name with single quotation marks.) You have saved the day!!

  7. Hi I have created a spreadsheet in Excel to control when employee's certifications are expiring. Then I have created seperate folders for each employee that contains the picture of the certifications. So I wanted to be able to click on the cell in excel and it be linked to the person's folder. The concern I have is obviously there are new employees added to this excel spreadsheet in alphabetical order. Therefore would the link automatically stay with the name? Example cell A2 is Doe, Jane but tomorrow that name could appear in cell B2. Is there a way to keep the link to the name?

  8. Hi I have created a spreadsheet in Excel to control when employee's certifications are expiring. Then I have created seperate folders for each employee that contains the picture of the certifications. So I wanted to be able to click on the cell in excel and it be linked to the person's folder. The concern I have is obviously there are new employees added to this excel spreadsheet in alphabetical order. Therefore would the link automatically stay with the name? Example cell A2 is Doe, Jane but tomorrow that name could appear in cell A3. Is there a way to keep the link to the name?

  9. I want to write a formula in one summary sheet of workbook, wherein the value comes from a fixed cell number of different sheets of the same work book. In other words the value of a fixed cell number gets updated in the summary sheet as and when new sheets gets updated. can I write a formula for adding sheets for sheet1, sheet2, sheet3 etc.. i.e. =sheet1+1!H37!!

    Regards,

  10. I have worksheet sheet1,sheet2 and sheet3 with big data. this sheets were added together in "summary sheet"
    ='sheet1'!C1+'sheet2'!C1+'sheet3'!C1
    now I added another sheet4 with the same rows and columns as "summary sheet" and want to sum up on the Summary sheet as
    ='sheet1'!C1+'sheet2'!C1+'sheet3'!C1+'sheet4'!C1
    but since my data is too big I can't manage doing all the rows and columns. so how can I state so that the whole "Summary sheet" add up sheet4 on the Previous one adding up same cells of sheet4 on it.

  11. I have worksheet sheet1,sheet2 and sheet3 with big data. this sheets were added together in "summary sheet"
    ='sheet1'!C1+'sheet2'!C1+'sheet3'!C1
    now I added another sheet4 with the same rows and columns as "summary sheet" and want to sum up on the Summary sheet as
    ='sheet1'!C1+'sheet2'!C1+'sheet3'!C1+'sheet3'!C1
    but since my data is too big I can't manage doing all the rows and columns. so how can I state so that the whole "Summary sheet" add up sheet4 on the Previous one adding up same cells of sheet4 on it.

  12. Hello,

    I have a macro that displays the name of each sheet in a column at the end. I want to display the value of A1 for each of those sheets. How can I do that?

  13. I want to refer a closed worksheet cell.

    ='D:\10187\CSS\[=BW169]PipCost'!$D$2.

    How to put filename with using formula?

  14. Hi,

    I am facing another issue in referecning using Index & Match. When I pass a reference of one sheet in the second sheet, the value becomes visible and my formula works on it. But if I change the position of the column by inserting another column in the first excel sheet, the formula stops working. I have tried it to resolve by passing the "Name" of teh list as a reference in the 2nd sheet instead of Fixed column, but the issue is not resolved. Can anyone help on this?

    Aftab

  15. Hi, i have an excel spreadsheet with office sales open all day and its linked to each departments excel workbook which has their sales on it. Currently i have to select update everytime i want upto date sales figures. Id like my excel book to update each time a department updates their sales without my telling the spreadsheet workbook to do it. Is this possible? The workbooks are all saved in a public drive and each team opens their workbook from the public drive on their pc and adds sales as they get them. Thanks

  16. Hello Svetlana Cheusheva,
    Your article is so useful and it helped me a lot learning insights of excel.

    How I can reference data from online uploaded excel sheet?

    Your quick reply would be highly appreciated.

  17. I am working with 32 sheets. need to draw information from different sheets 6 times in each column. is there a way to name the sheet 1 time instead of naming the sheet 6 times? there is only one other sheet for each column that data is drawn from. I tried "sheet name" in c5 and tried to write the formula =c5'!b23 with no luck.

  18. Thanks a million! Note that it is much safer to use named references when linking to external sheets.

  19. Is there a way to set a value in an external reference. All of the examples I see so far are retrieving references from an external. I would like to be able to update the external value.

  20. so efficient

  21. Hello

    Is there any way to have a variable within the file name of an external link?

    For example:

    File A has cells that link to File B.

    The next month it needs to change from April to May. Rather than change the linking in each cell to point to the new file I would like to have a cell in file A where I just Type "May" instead of "April" and the links automatically update the file direction to May.

    Below is the example- Ideally I would love to have a cell in File A where I just put in "05 May" and "May" and it updates the changes below in all formula so I don't have to manually go through and change everything. (There are hundreds of linked sheets... they all change by only the month in naming and saving convention)

    April link
    ='\\2016 Sheets\04 Apr\[April 1 2016.xlsm]Purchases-Sales'!$K$13
    May Link
    ='\\2016 Sheets\05 May\[May 1 2016.xlsm]Purchases-Sales'!$K$13

    1. Kelly,

      Please try using the nested function
      INDIRECT(CONCATENATE()).

      For example put your variables in A1 and A2
      =INDIRECT(CONCATENATE("'\\2016 Sheets\",A1,A2,
      "Purchases-Sales'!$K$13")

  22. Good morning,
    I am working with 2 sheets. One is taking all the information to the other.

    But, when I try to put in different order, it goes wrong.

    How can I do to have different SORT in both tables or sheets?

    1. If you're having issues when sorting the source data, i'm suspicious you're using VLOOKUP.
      try switching to Index/Match and you'll never look back!
      =INDEX("Range with values you want",MATCH("Specific Value","From this Range",0))
      you can move those columns anywhere you want and the formula still works. You can use cell references, structured table references, or even named ranges

  23. How about if you are working on excel online? Thank you.

  24. Hi Svetlana,

    I have a unique problem, we created a [Planner.xlsx] template to enter our weekly sales. So instead of creating a new excel sheet every time, we save the template as a [Planner 25-04-16.xlsx]. We also have 3 other sheets to Planner.xlsx and save those sheets similarly as well. I recently started to externally reference the 3 sheets to Planner.xlsx and everything works fine. But when I save all the sheets as Planner 25-04-16.xlsx, Book1 25-04-16.xlsx, Book2 25-04-16.xlsx, and Book3 25-04-16.xlsx; the formulas still reference to PLanner.xlsx, but I want all of them to reference to the weekly sheets. Is there a way to do this without manually changing the references on a weekly basis.

    Thanks in advance.

  25. I have 4 tabs (1stwk, 2ndwk, 3rdwk, 4thwk) wherein I need the info from B2 of each tab to be totaled and entered on my final tab. I tried =SUM(April25!Sheet1!Sheet1!Sheet1!A7D9+April18!b2+April11!b2+April4!B2) but didn't work. What am I doing wrong?

    1. You have to try this simple one,

      =SUM(Sheet1!B2,Sheet2!B2,Sheet3!B2,Sheet4!B2)

  26. I would like to reference an entire tab in one worksheet as a tab in another worksheet.
    is that possible? how?

  27. Hi, great, helpful posts. I was wondering if you could help me with an INDEX/MATCH problem where all the information I am referencing in sheet A is actually in sheet B, so only the cell/reference in sheet A in "MATCH" is actually in the sheet where I am building my function, but I cannot figure out a way to reference the column number correctly for the INDEX function as it resides in a different sheet simply typing the column number clearly does not work... Thank you!

  28. Svetlana Cheusheva
    I receive a number of excel files (on a weekly basis) and one file could have up to 1000 entries of items purchased.Information of 1 particular item is contained in 1 file.
    When i receive items, am supposed to extract information of particular items from the files i received. However, not all items received in a particular consignment are contained in one file, i would be forced to go through 10 files to get all the information.
    I would wish to come up with a file where i will only have to type in product code and container number and information is extracted from the weekly sheets stored in one folder.

  29. I have come up with a cell reference using the ADDRESS formula. Is there anyway to use this when referencing another sheet in a workbook?

    So instead of:
    ='Ex workbook'!A1

    Something along the lines of:
    ='Ex workbook'! & ADDRESS(1,MATCH(WEEKNUM(F1),'Ex Workbook'!$1:$1,0))

    I added the & to show where I need to join the two halves of the reference. Thanks!

  30. Below is an example of my formula.

    ='All-Pr X Wght'!EF$3/'All-Pr X Wght'!$B$3*100

    I cannot get 'EF$3' to automatically change to 'ef$4' when copying down a column?

    Why?

    1. Hi Anna,

      Because you fixed the row reference with the dollar sign. When you add $ in front of a column and/or row coordinate, it locks that coordinate(s), and the reference does not change no matter where the formula is moved or copied. Just change it to EF3 and the reference will adjust based on the relative position of a cell where the formula is copied. For more information about absolute and relative cell references, please check out the following tutorial: Why use dollar sign ($) in Excel formulas - absolute and relative cell references.

      1. hii....i just want to know whether i can get all the values of a data in a page by making any reference in a dropdown box...ie..... if i select any name created in a dropdown box, it should show all the values related to that name in another page.

  31. Hi Felipe,

    You can download the free add-in "Power Query" if you have Excel 2013. Once the link is established between the two excel sheets, workbooks within the network or even an external location, the excel workbook reading the data will automatically update once the source excel sheet is saved [whether open or closed].
    Try this as this is very effective to produce reports for data flowing from other departments/braches etc., and even from the website.

    Regards,
    Ramki

  32. Hi Svetlana Cheusheva!

    Awesome post!

    I was wondering to link tables "formated as table" or named ranges in another workbooks! Is there a way of doing it?!

    1. Hello: I have two worksheets. I have followed your naming convention in referncing another worksheet cell contents so that it shows in the 2nd worksheet. I have names and addresses, etc. in the first sheet. When I complete the formula, any blank cell in the first sheet is showing a "0" in the cell instead of keeping it blank. Is there something to put in the formula, or a way that will not show the zero unless there is actually a zero in the cell that I want to show?
      Thank you.

    2. Hi Felipe,

      Of course, you can do this.

      To link to an Excel table located in another workbook, you can create a so-called "structured reference" consisting of the workbook name, table and column names, e.g.:

      =SUM(Book1.xlsx!Table1[Sales])

      Where [Sales] is the name of the column you want to sum. If the workbook name contains spaces, remember to enclose it in single quotes, e.g.:

      =SUM('2016 sales.xlsx'!Table1[Sales])

      In fact, you don't have to memorize the syntax of the structured reference. Simply select the cells in the table when typing your formula as you usually select ranges, and those names will appear in the formula automatically.

      As for a reference to a named range in another workbook, please see the following example in this tutorial: Referencing a name in another workbook.

      1. I just need to retrieve those data without sum function. Is that possible

      2. WOW, nice!

        Is there any workaround while referencing closed workbooks? Cos, SUMIF and COUNTIF and Named Ranges isnt working, returning an error. That's the matter!

        1. Felipe,

          When referencing a closed workbook, you have to include the full path, like:

          =SUM('D:\Documents\Book1.xlsx'!sales)

          Where "sales" is a named range in Book1.

          1. Hi Svetlana Cheusheva,

            I did all this things, but none of them works fine! It might be something Office versions. I'm using 2016 version! And, does not work, even in another computer. If I use =SUM('D:\Documents\Book1.xlsx'!B:B) instead of =SUM('D:\Documents\Book1.xlsx'!sales), works!! Really weird!

            Ramki,

            I have downloaded PowerQuery and it works! But, this SUM will be performed by another person, which may not like to use it. But I'll try to show this feature.

            1. Felipe,

              Yep, this is really weird. Your first reference should not work by any means, because the sheet name is missing. You probably meant something like:

              =SUM('D:\Documents\[Book1.xlsx]Sheet1!'B:B)

              And I can think of only one reason for the named range reference not working - you've created a worksheet-level name (please check the Scope parameter of your named range). If it is the case, then add a worksheet name to the reference.

              Or, open the other workbook and create a reference to your named range by selecting it using the mouse. Then close that other workbook, and Excel should make the required corrections to the reference automatically. (For what it's worth, I'm using Excel 2013.)

              1. heloo admin ...please contact me on my email ...i have some questions about excel formulas ..thanks and regards

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)