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

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

  2. 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. Alexander,
        Thank you for replying, but bad news, I tried both and its still not working! Please see below for more info, as well as another question on something totally different (obviously you seem to be an excel expert, doesn't hurt to ask while I have you):

        - The Excel-file-options-advanced-update things was already checked. The data-edit links thing was checked as "automatic" not manual, so good there too.
        - Not sure if its helpful, but on that edit links area it gives options to the right (update values, change source, open source, break link and check status). The "Sources" listed to the left shows the correct filename (I've used "closedworkbook.xlsx" and "openworkbook.xlsx"), type: worksheet, Update: A, and Status: Unknown. When I click on Check Status the status for each instantly changes to Error: Source not found (which I think is wierd...both files are saved on my PC, both saved right next to each other on the desktop, have not been deleted, etc. When I click on Update Values it opens the file explorer type box for me to assign the file again, which I do, then the status changes to "OK". Then when I click check status again it goes back to error source not found.

        OK, second totally separate issue. I'm developing a somewhat elaborate spreadsheet (for me as a novice) that has formulas pulling data from other sheets within the same file. Please let me give you an example of what I'm trying to accomplish and then the issue (b/c it actually works fine until the issue):
        - Sheet 1 has the base data. There are 12 columns and 30 rows worth of it. One column (D) is "Jan" and has 4 separate peoples names, in no particular order (Ex: 1 Tom Smith, 3 Sue Jones, 6 Nick Thomas and 20 Lisa Brown). Tom, Sue, Nick, Sue, Lisa, Lisa, Lisa, Sue, Lisa, etc.
        - Sheets 2 through 5 are named Tom, Sue, Nick and Lisa, and I'm using a formula to auto-populate data from Sheet Jan, bringing over all of the data pertaining to that specific person. On Sheet Tom, the formula I'm using is =FILTER(Jan!A:L,Jan!D:D="Tom Smith"). Works PERFECTLY! Then when I email the spreadsheet to a colleague it changes the formula to =_xlfn._xlws.filter($Jan.A:L,$Jan.D:D="Tom Smith") and doesn't work. Tried it on different comps, different versions of excel, tried it on libre office, etc.

        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.

  3. i use something like =IF(OR(A43="",C$42=""),"",INDIRECT("'" & A43 & "'!" &C$42))
    This allows me to in the A43 to Type the tab name Say Tab1 Tab2 Tab3 (Spelled the same) and in the C42 spot point the cell i want data from C(Column) 42(Row)
    This allows me to pull total from any page and any location. Without alot of Copy paste link
    This all checks if the Sheet name exists returns an error if does not

  4. How to make cell to be a search engine??
    with a thousands of data you want to search

  5. Hello helping,
    I am not educated in Excel but always looking into it possibly me. I have an instance where I want to tell the sheet:
    I have a drop down with 5 different module names(CMA1, CMA2,CMA3,CMA4, CMA5)
    when I select one from the drop down, say if CMA2 is selected and displayed in A3 then I want C3 - C26 to display the information on the following tab (CMA Cell Sheet) that is in the workbook sheet K4 - K26. I have tried:
    =IF(A3="CMA2",'CMA cell sheet'!K4:K26)
    but I get a "VALUE" error pop up.
    I would like to the same for CMA1, CMA3, CMA4, CMA5 but with different rows of information for each on the same CMA Cell sheet.
    I hope that you can help me with this.

  6. how to copy particular one cell value to another sheet

  7. I have done it as described. It works across workbooks. However, the moment I make a change to workbook B, sheet B - and if it is only entering a value in any cell - and then save and close B.xlsx, I lose my value in A.xlsx/A-sheet-cell. The cell shows a #Bezug (in German) where the sheetname B should be. The rest of the link still shows correct, but the sheet-name got lost and was replaced by #Bezug.
    What is going on????

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

  9. Is there a way to replace the filename with the value of another cell

  10. To increment the cell you'll need to use the CELL() function.
    Dynamic Sheet: =INDIRECT(sheet&"!"&CELL("address",A1))
    Dynamic Workbook: =INDIRECT("'["&workbook&"]"&sheet&"'!"&CELL("address",A1))

    For me it looks like this: =INDIRECT("'["&$D$1&"]"&$B$1&"'!"&CELL("address",B6))
    In D1 i have the file/workbook reference and in B1 I have the sheet name.

  11. how to make the increment of sheet reference when drag down from different file .
    My point is to auto update in one worksheet with different position of cell from another multiple sheets with fixed cell in arrangement of sheet.

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

  13. Hi
    I want to get the data from another workbook. path is as below, but it doesn't works.

    =('E:\K.E\Actions\All Bills\PM Invoices\Service Bills July 2019 To June 2020\Punjab\[(43-19) Rope Tightness job at NP Plant FFCL.xlsx]Invoice Format-Sales Tax]' !A9)

  14. I’m not very excel savvy so please forgive me. I’ve got 8 tabs with information, not numbers, and I need one master tab (tab #9) to be a running total list of all the other 8 tabs. So when someone inputs information on one of the eight tabs it will automatically be added to the master tab. Is there a way to do that? If so how do I set it up in layman terms?? Excel is not my favorite program to work with and I am not to familiar with coding. So the more simpler you can explain it I would really appreciate it.

  15. Can Anyone Help Me How To Combined 1500 Sale Invoice Sheets Data Into One By Putting Single Formula Just??
    Example Sheet 1, sheet 2 in Last 1500 sheets?
    Date..
    Invoice no..
    RAtes...
    AMount...
    Anyone? I try vlookup but didn't work..

  16. Can anyone help me with Vlookup formula

  17. I have a workbook that must pick up values from another workbook. The source workbook name is not known so I want the user to enter its name on the output sheet once then all the formulas should pick up that name as part of its formula.
    So instead of =SUM([Sales.xlsx]Jan!B2:B5) I want the formula to pick up a value from a cell:
    Cell A1 = "Source.xlsx" Answer cell is SUM(["value of A1"]Jan!B2:B5).
    How do I write this answer cell please?

  18. Does someone know what I'm doing wrong?

    Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    Dim printDate As Date
    Dim startDate As Date
    Dim endDate As Date
    startDate = H5 //cell on the same sheet as the button
    endDate = H6 //cell on the same sheet as the button
    For printDate = startDate To endDate
    ActiveSheet.Range("Sheet2!J4") = printDate
    ActiveSheet.PrintOut , Copies:=2
    Next
    Application.ScreenUpdating = True
    End Sub

  19. I have a master sheet with all data and i want to have separate sheets which only pulls certain data from the master sheet. How can i go about doing that?

  20. Hi

    Thanks for an informative article.
    Can you please tell me if it is possible to link to an external workbook that has a name change each day. I want to draw information from multiple cells without having to rewrite the whole spreadsheet each day.

  21. In Worksheet B I want to reference a cell in Worksheet A that is a sum of a list - let's call is cell B12. Can I add to that list in Worksheet A over time and have the reference work? When I do it now, Worksheet B picks up whatever is in B12, it doesn't adjust to know that the new sum is now in B14 because I added to lines to the list that is being summed up. Thanks!

  22. Good Afternoon,

    I have a bit of a complicated question. Im doing a run-down of clients and basically need Sheet 1 A1-A10 (Clients names) to transfer to Sheet 2 A1-A10. But the catch is, I do not want the clients to erase from Sheet 2 when I delete them on Sheet 1. I need them to just pile down on Sheet 2 from A1- A90. Not sure how to though. Any help would be greatly appreciated.

    Thank you

  23. i want transfer all exceldata to another excel sheet but without formula. give me quick reply

    1. dhaval:
      The quickest way to copy data only from one sheet to another is to first copy the original data then in the new sheet right click where you want the data and select paste special values.

  24. Good morning,
    I have created a "Database" workbook to use as my source workbook and linked it to other workbooks as reference, in the hopes that when I update the database, the information will be pulled to other workbooks. I understand that the "database" workbook has to be opened at the same time as the workbook that i am working with, but do both workbooks have to be in the same folder?

  25. Hello

    I have one problem

    we have many answers in row like

    This eq I would like to take only one answer to show in final

    this rows around 800 so each like checking is difficult, IF any answer removed, the final cell it has to show removed.

    Eg.

    R1 R2 R3 r4
    Removed Not Removed not removed not removed

    In final R5 I would like to get one answer either removed/ not removed

  26. Hello
    I have to folder one folder name is january and secound folder name is February in my january folder i have one excel file in this file i have 4 column in first column i have opening balance, secound column i have institution, third column i have disposal or forth column closing balance. in my second folder all column or row are same opening balance, institution, disposal or closing balance but in this folder i want to link my opening balance with another folder january closing balance when i change figure in my january folder than automatically change my secound folder opening balance. i want to link my january folder excel file closing balance with my second folder February excel file opening balance. Is it possible

  27. How to reference external several files?

    There are 10 files named A001, A002, ...., A010.

    Can I make the file names in general instead of typing each name on an another excel file?
    For example,

    ='D:\Reports\[A001.xlsx]A001'!B1 (file name and sheet name are same)
    --->
    ='D:\Reports\[search(....).xlsx]search(....)'!B1

    My idea is that to have excel search the correct file and reference the cell value.
    Can it be possible?

  28. Hello mam, i really need your kind support..

    What if i wrote date January 23, 2018 for example and i want automatically on the other sheet, the column for january in the other sheet will have color depend on what i desire. please..
    for February same above will happen.

  29. I need to know one sheet data details to get other sheet as a sammery list
    ex:
    A company - How many delays are there / how many case open are there/ how cases finished on time

  30. Hi,

    i have two sheets in a work book i.e Sheet1 and sheet2. in sheet1 i have the data and in sheet2 i have put "sumifs "formula and it works.now i have to delete the data in sheet1 and update new data of the same type.

    but when i do this the formula doesn't work in sheet2.

    Please tell me the solution.
    Thanks

  31. I am trying to reference a sheet within the same workbook by entering the following formula : =Sum('Monthly Bill (OCT)'!C2:D2) I receive a #REF response. Can you help me figure out whats wrong with my formula.

  32. i want to make register like issue for proccess in excel with that i also want to make same register of party that i have given material to that party for example

    workgroup

    issue to process (sheet name)
    date
    issue no
    party
    quantity
    rate
    amount

    party register (sheet name)
    date
    qty
    rate
    amount

    at one time i want to add record in party register when i issue to process
    data....

    waiting for reply.............

    alpesh

  33. Hello, I wanna know if we can make a relative folder referencing?
    Like, I want to sum the cells from the parental folders?
    Thank you!

  34. I am trying to sum values from one worksheet to another and its giving me 0 which is wrong.It copies the cell values and sheet name correctly in sum but as soon as i press enter,it turns to 0.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  35. I want to use Sheet 1 to enter data, Sheet 2 to store formulas, and Sheet 3 to store the calculated output from Sheet 1 data modified by the formulas in Sheet 2.
    How to do this?
    Thanks

    1. Hello,

      If I understand your task correctly, please try to do the following:

      1. Enter number 1 in cell A1 on Sheet1;
      2. Enter the following formula in cell A1 on Sheet2:
      =Sheet1!A1+Sheet1!A1
      3. Then enter the formula below in cell A1 on Sheet3:
      =Sheet2!A1

      Hope this will help.

  36. hello. i am confused. what i am doing is exactly what is stated in the above examples. however, instead of the actual value, the formula text is the one displayed.

    ex. =sheet1!h10 displays sheet1!h10 instead of the value of the cell. is there a workaround on this?

    thanks!!!

  37. HELLO,

    I don't want to add the values using the SUM formula, I just want to copy the cell values from another workbook. how do i do this.

    1. Hello,

      You can add references to values from another workbook by copying the cell values from this workbook and pasting them into the workbook you need via Paste Special –> Paste Link.

      Hope it will help you.

  38. i need help, is it possible to link a cell from sheet1 to the values on sheet2??for example if i input a name on sheet1 cell A1 and matches a name on a list on sheet2 then the name will turn to " color red" or "error" if matches??thanks in advance..

  39. Thank you!! you've helped me

  40. could anyone help to find out the path of reference cell of different sheets? is there any way to reach the reference sheet quickly

  41. Hi! Just have a further query: I'm required to fill out two timesheets for work with the same times, and have successfully used your 'External reference to a closed workbook' guide above to fill out the second timesheet with the times automatically pulled from the first timesheet.

    However, the 'first timesheet'/reference workbook has each weekly entry made as a new tab/worksheet (i.e. each worksheet in the source workbook covers a week and is named with a date range), so in my second timesheet, I have to update the workbook name in every since relevant cell each week to get it to fill out automatically.

    So I suppose my question is: in the formula

    [Workbook_name]Sheet_name!Cell_address

    is there a way to reference merely the top-level/latest worksheet rather than the specific sheet name, so I don't have to change it manually?

    Thanks!

  42. Hello I receive an excel file but when I open the file , it says, This workbook contains links to one or more external sources that could be unsafe.
    If you trust the links, update or dont update. There is an error: source not found . There is a file from the link that i couldnt find, i have only receive one file for example file1.xlsm and the on the edit link it is looking for example file2.xlsm. Does this mean i need to create a new file formula ? Thank you very much i new to this thing.

  43. How do I do a comparison?
    For example, I have 'sheetA' that has a unique value in cell D1.
    I then have sheetB that has a list of values in column D.
    I want to set something that will flag cell D1 in sheetA green if it shown in column D of sheetB, or red if that value is not shown in that column.
    Can this be done?
    Thanks, M

  44. I have 2 Sheets, Sheet1 is for record entry with SAVE RECORD button and once button is clicked it updates record in Sheet2 which works well, but the problem is that after SAVE RECORD button click it display sheet2 where the data is saved which i do not want. I need Record to be saved without displaying sheet2. please help me.

  45. Hi,
    The Problem faced by me in excel that I have a sheet in which some things typed i want that on another sheet i type first name one a time and complete things automatic come. and next time when that name enter on first sheet other sheet automatic update without putting name.
    Can you help.
    Waiting your kind response.

    BR

  46. The problem faced by me in excel is regarding linking of cells.actually what happens is after linking a cell in excel from same workbook but from other sheet,and clicking on the linked cell just shows the path and does not open the linked path.
    thanks in advance.

  47. Hi guys
    Can't find info regarding the action of excel reference to another ONLINE workbook.
    I am having two different workbooks non related and trying to gather results from workbook one to be displayed in the workbook two.

    Please help
    Cheers,
    Claudia

  48. Hello,

    My question is: I have a spreadsheet that has two tabs. I want the first tab to pull appointment dates from the second tab if the patient names on both tabs match up. How do I do this to save myself countless hours? Thank you in advance for your assistance.

    1. Rajesh Kumar was able to figure out how to do the formula that I need to save me countless hours! Thank you so much.

  49. helloo,
    i have a problem below;

    sheet 1 (all the data present with merge cell)
    ---------------------------------------------------
    XTP0101 922367******9111 1,000

    sheet 2
    ---------------------------------------------------
    ='sheet1'!C13:D13

    Result:
    ---------------------------------------------------
    #VALUE!

    Which formulae to use?????

  50. Hi everybody,

    Im an intern at an engineering company in the Netherlands and working on my 'personal investigetion'. I'd like to apolagize in advace for any mistakes regarding my English.

    At the moment I've exported a shedule from Revit2016 to Excel2016. It has 8 rows of information (headings excluded) and 62 columns. It's my intention to make a seperate Format in Excel which is able to extract data from these 496 cells and convert it into grouped Metadata.

    The problem is that a Format has to be able to be apliccable to every project, each of them with their own specific number of rows (column headings are identical). Besides, the workbook_names from which the data is extracted differ every time.

    I had the intention to create a worksheet with formula linking cells to the extract file, but to write the formula in such a way that it is easy to manipulate.

    For example: =('[Workbook_name]Worksheet_name!Cell_name') becomes =('[Cell_name]Cell_name!Cell_name) with the first two Cell_name referring to Cells within the current Worksheet in which the Workbook_name and Worksheet_name can be edited.

    Unfortunately I hit a brick wall, it doesn't work. Perhaps I'm not doing it right or it just isn't possible.

    If someone is able to help, please do! I've got to have the Format ready a week from now (with 01-06-2016 being today).

    With Kind regards,

    Roy W.

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