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.

When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.

External reference in Excel is a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.

Although external references in Excel are very similar to cell references, there are a few important differences. In this tutorial, we'll start with the basics and show how to create various external reference types with detailed steps, screenshots and formula examples.

How to reference another sheet in Excel

To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address.

In other words, in an Excel reference to another worksheet, you use the following format:

Reference to an individual cell:


For example, to refer to cell A1 in Sheet2, you type Sheet2!A1.

Reference to a range of cells:


For example, to refer to cells A1:A10 in Sheet2, you type Sheet2!A1:A10.

Note. If the worksheet name includes spaces or non-alphabetical characters, you must enclose it in single quotation marks. For example, an external reference to cell A1 in a worksheet named Project Milestones should read as follows: 'Project Milestones'!A1.

In a real-life formula, which multiplies the value in cell A1 in 'Project Milestones' sheet by 10, an Excel sheet reference looks like this:

='Project Milestones'!A1*10

Creating a reference to another sheet in Excel

When writing a formula that refers to cells in another worksheet, you can of course type that other sheet name followed by an exclamation point and a cell reference manually, but this would be a slow and error-prone way.

A better way is point to the cell(s) in another sheet that you want the formula to refer to, and let Excel take care of the correct syntax of your sheet reference. To have Excel insert a reference to another sheet in your formula, do the following:

  1. Start typing a formula either in a destination cell or in the formula bar.
  2. When it comes to adding a reference to another worksheet, switch to that sheet and select a cell or a range of cells you want to refer to.
  3. Finish typing the formula and press the Enter key to complete it.

For example, if you have a list of sales figures in sheet Sales and you want to calculate the Value Added Tax (19%) for each product in another sheet named VAT, proceed in the following way:

  • Start typing the formula =19%* in cell B2 on sheet VAT.
  • Switch to sheet Sales, and click on cell B2 there. Excel will immediately insert an external reference to that cell, as shown in the following screenshot:
    Creating a reference to another sheet in Excel
  • Press Enter to complete the formula.
Note. When adding an Excel reference to another sheet using the above method, by default Microsoft Excel adds a relative reference (with no $ sign). So, in the above example, you can just copy the formula to other cells in column B on sheet VAT, the cell references will adjust for each row, and you will have VAT for each product correctly calculated.

In a similar manner, you can reference a range of cells in another sheet. The only difference is that you select multiple cells on the source worksheet. For example, to find out the total of sales in cells B2:B5 on sheet Sales, you would enter the following formula:

Creating a reference to a range a cells in another worksheet

This is how you reference another sheet in Excel. And now, let's see how you can refer to cells from a different workbook.

How to reference another workbook in Excel

In Microsoft Excel formulas, external references to another workbook are displayed in two ways, depending on whether the source workbook is open or closed.

External reference to an open workbook

When the source workbook is open, an Excel external reference includes the workbook name in square brackets (including the file extension), followed by the sheet name, exclamation point (!), and the referenced cell or a range of cells. In other words, you use the following reference format for an open workbook reference:


For example, here's an external reference to cells B2:B5 on sheet Jan in the workbook named Sales.xlsx:


If you want, say, to calculate the sum of those cells, the formula with the workbook reference would look as follows:


External reference to a closed workbook

When you reference another workbook in Excel, that other workbook does not necessarily need to be open. If the source workbook is closed, you must add the entire path to your external reference.

For example, to add up cells B2:B5 in the Jan sheet from Sales.xlsx workbook that resides within the Reports folder on drive D, you write the following formula:


Here's a breakdown of the reference parts:

  • File Path. It points to the drive and directory in which your Excel file is stored (D:\Reports\ in this example).
  • Workbook Name. It includes the file extension (.xlsx, .xls, or .xslm) and is always enclosed in square brackets, like [Sales.xlsx] in the above formula.
  • Sheet Name. This part of the Excel external reference includes the sheet name followed by an exclamation point where the referenced cell(s) is located (Jan! in this example).
  • Cell Reference. It points to the actual cell or a range of cells referenced in your formula.

If you've created an reference to another workbook when that workbook was open, and after that you closed the source workbook, your external workbook reference will get updated automatically to include the entire path.

Note. If either the workbook name or sheet name, or both, include spaces or any non-alphabetical characters, you must enclose the path in single quotation marks. For example:

=SUM('[Year budget.xlsx]Jan'!B2:B5)

=SUM('[Sales.xlsx]Jan sales'!B2:B5)

=SUM('D:\Reports\[Sales.xlsx]Jan sales'!B2:B5)

Making a reference to another workbook in Excel

As is the case with creating an Excel formula that references another sheet, you don't have to type a reference to a different workbook manually. Just switch to the other workbook when entering your formula, and select a cell or a range of cells you want to refer to. Microsoft Excel will take care of the rest:
Making a reference to another Excel workbook


  • When creating a reference to another workbook by selecting the cell(s) in it, Excel always inserts absolute cell references. If you intend to copy the newly created formula to other cells, be sure to remove the dollar sign ($) from the cell references to turn them into relative or mixed references, depending on your purposes.
  • If selecting a cell or range in the referenced workbook does not automatically create a reference in the formula, most likely the two files are open in different instances of Excel. To check this, open Task Manager and see how many Microsoft Excel instances are running. If more than one, expand each instance to view which files are nested there. To fix the issue, close one file (and instance), and then open it again from the other file.

Reference to a defined name in the same or another workbook

To make an Excel external reference more compact, you can create a defined name in the source sheet, and then refer to that name from another sheet that resides in the same workbook or in a different workbook.

Creating a name in Excel

To create a name in Excel, select all the cells you want to include, and then either go to the Formulas tab > Defined names group and click the Define name button, or press Ctrl + F3 and click New.

In the New Name dialog, type any name you want (remember that spaces are not allowed in Excel names), and check if the correct range is displayed in the Refers to field.

For example, this is how we create a name (Jan_sales) for cells B2:B5 in Jan sheet:
Creating an Excel name

Once the name is created, you are free to use it in your external references in Excel. The format of such references is much simpler than the format of an Excel sheet reference and workbook reference discussed earlier, which makes the formulas with name references easier to comprehend.

Note. By default, Excel names are created for the workbook level, please notice the Scope field in the screenshot above. But you can also make a specific worksheet level name by choosing a corresponding sheet from the Scope drop-down list. For Excel references, the scope of a name is very important because it determines the location within which the name is recognized.

It's recommended that you always create workbook-level names (unless you have a specific reason not to), because they significantly simplify creating Excel external references, as illustrated in the following examples.

Referencing a name in another sheet in the same workbook

To reference a global workbook-level name in the same workbook, you simply type that name in a function's argument:


For example, to find the sum of all the cells within the Jan_sales name that we created a moment ago, use the following formula:


To reference a local worksheet-level name in another sheet within the same workbook, you need to precede the name with the sheet name followed by an exclamation mark:


For example:


If the sheet names includes spaces or mon-alphabetic chars, remember to enclose it in single quotes, e.g.:

=SUM('Jan report'!Jan_Sales)

Referencing a name in another workbook

A reference to a workbook-level name in a different workbook consists of the workbook name (including the extension) followed by an exclamation point, and the defined name (named range):


For example:


To reference a worksheet-level name in another workbook, the sheet name followed by the exclamation point should be included as well, and the workbook name should be enclosed in square brackets. For example:


When referencing a named range in a closed workbook, remember to include the full path to your Excel file, for example:


How to create an Excel name reference

If you have created a handful of different names in your Excel sheets, you don't need to remember all those names by heart. To insert an Excel name reference in a formula, perform the following steps:

  1. Select the destination cell, enter the equal sign (=) and start typing your formula or calculation.
  2. When it comes to the part where you need to insert an Excel name reference, do one of the following:
    • If you are referring to a workbook-level name from another workbook, switch to that workbook. If the name resides in another sheet within the same workbook, skip this step.
    • If you are making a reference to a worksheet-level name, navigate to that specific sheet either in the current or different workbook.
  3. Press F3 to open the Past Name dialog window, select the name you want to refer to, and click OK.
    Creating a reference to an Excel name in another workbook
  4. Finish typing your formula or calculation and press the Enter key.

Now that you know how to create an external reference in Excel, you can take a benefit from this great ability and use data from other worksheets and workbooks in your calculations. I thank you for reading and look forward to seeing you on our blog next week!

You may also be interested in

267 comments to "How to create external reference in Excel to refer to another sheet or workbook"

  1. Shahab Ahmad Kiyani says:

    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)

  2. Jkjeepgirl says:

    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.

  3. Awais Mughal says:

    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?
    Invoice no..
    Anyone? I try vlookup but didn't work..

  4. Pete Sacker says:

    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?

  5. Sparky says:

    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
    Application.ScreenUpdating = True
    End Sub

  6. Rojan Nepal says:

    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?

  7. Paul Varney says:


    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.

  8. Sue says:

    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!

  9. Danny says:

    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

    • Doug says:

      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.

  10. Claudia says:

    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?

  11. HARRY says:


    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.


    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

  12. Kamlendra Singh says:

    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

  13. joe says:

    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)

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

  14. Ramil says:

    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.

  15. hasa says:

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

  16. Haroon says:


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

  17. Angie says:

    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.

  18. alpesh says:

    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


    issue to process (sheet name)
    issue no

    party register (sheet name)

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

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


  19. Sonnie says:

    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!

  20. KG says:

    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.

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

  21. Peter says:

    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?

    • 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:
      3. Then enter the formula below in cell A1 on Sheet3:

      Hope this will help.

  22. johann says:

    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?


  23. TIASHA says:


    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.

  24. chris says:

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

  25. saji says:

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

  26. Hannah says:

    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


    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?


  27. Rodel says:

    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.

  28. Mark DE says:

    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

  29. Qazi says:

    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.

  30. Zeeshan Ali says:

    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.


  31. mathew says:

    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.

  32. Claudia Badea says:

    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

  33. Connie says:


    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.

    • Connie says:

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

  34. biplave says:

    i have a problem below;

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

    sheet 2


    Which formulae to use?????

  35. Roy W. says:

    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.

  36. Brad Loudermilk says:

    I have 2 different workbooks that I am trying to link that have different file paths. I have a "Log" and a "Tracking" workbook. In the "log" there are hyperlinks only in column B that show as numbers because they reference what the next document # should be (0001-5000). When you click on the hyperlink to open up the excel file (tracking document), I need it to always have that reference #. Example- If I click 0005 and the hyperlink opens the tracking workbook, then I need it to have 0005 in a certain cell. This goes for the same way for every number that you can click. How do I link these workbooks together so that the tracking document always has the number that you click on?

  37. Samantha says:


    I have 2 workbooks.. one I input data into, and the other logs who made the error.. is there a way I can make it so whenever I put someone's name into it, it will automatically update the second workbook with the date it happened and how many?

  38. Noel says:

    Can u help me with this.
    if B2 is not available, i want to use the c2 to display the content coming from the sheet name IW39.
    if the c2 is not available, i want to use the B2 to display the content coming from the sheet name IW29.

    I will have 3 sheet. Namely "Data info", "IW39" AND "IW29"

  39. Shahzabe ahmed says:

    Hi thanks for the tutorial. is there a way for you name the sheet that a formula is getting its information from.
    for example im trying to find the cheapest vendor for a service i offer, their rates are on different work sheets, then i have a master sheet that displays the cheapest rates for each product using vlookup. is there a way of naming the sheet that vlookup has found the data in?
    this would help me very much thank you.

  40. AmyF says:

    I have a spreadsheet that pulls information from one tab to another based on an if then formula. Is there a way to lock the information on the third page once it has been recorded even if it is deleted from the original tab?


  41. Ayodeji says:


    I have a sum that is derived using data from another workbook. However, when the other work book is closed. The formular reports an error.

    Both files are in the same folder on my desktop. Kindly assist.

  42. Somasundaram says:

    I need sheet 1 entire values in sheet 2.
    Sheet 1 having datas but sheet 2 is empty in initial stage.
    While we giving conditions in sheet 2, fetching datas in sheet 1 has to come automatically in sheet 2.
    for example,
    Sheet 1
    Name Age Country
    A 10 India
    B 10 USA
    C 10 India

    I need in Sheet 2
    Country = India datas
    Name Age Country

    Please do the needful for this

  43. Richa says:


    Currently i am preparing a job sheet in which i required help.
    As currently i have prepared 5 sheets in those sheets there are some different values or some similar values. Now i wanted to create one new sheet with such formula that if i will search any value then on that new sheet it will shows me the value location of all the 5 sheets that where it is placed. I wanted to do to save the time because for searching the values on whole workbook it consume time.

    For ex: I have value of OD56782 on 3 sheets out of 5. Now whenever i entered OD56782 on the new sheet it will display the locations of this particular value.

  44. Erin says:

    I am linking a cell between worksheets in the same file. When I type the formula in the cell 'Sheet 1'!H11 the cell is populated with a 0 since the cell I am linking too is blank. How do I remove this 0 from the cell with the formula? The linked cell is blank until populated which will then be carried over to the other sheet.


  45. satendra kumar says:

    Sir, kindly make for me one formulla based sheet ( opening+receving-damage-consuption= 'closing) sheet plz let me how to make

  46. DJB says:

    Hi Svetlana,

    I have a query that has me stumped. I am a bit of an excel novice so it may be nothing. I am currently trying to create a comprehensive meal planner/macronutrient calculator for my fitness training. What I want to do is have two separate sheets:
    The first I want to be a table that has all the ingredients i use in the first column, and then the Fat/Carb/Cal/Protein contents (1% of) in the subsequent columns directly relating to the quantity column at the end so I can change this value and all the macros will change accordingly. This I have managed to do.
    The second page, I want to have 6 individual tables laid out: INGREDIENT/CALORIES/CARBS/FAT/PROTEIN/QUANTITY. I would then like the ingredient column to be a drop down list that would include all the ingredients from the other sheet, and once selected, would autofill the remaining columns - this way I could easily add ingredients in the morning when I make up my meals for the day without having to individually input the stats. Any advice would be appreciated.



  47. Vasya says:

    Hi Svetlana,

    Question: How to make an external reference and to get the full format from the referenced cell: number, alignment, font, border and fill formats? Is there a way or formula that can do that? The target is not to get only the value from the referenced cell, but also the apearence of that cell.


  48. Deb says:

    I have two different workbooks. One with data source MSFGS.xlsx and another one where I would like to count the number of items, say for a particular month 'November' from the MSFGS.xlsx

    When the MSFGS.xlsx is closed the below formula is not working:

    =COUNTIFS('C:\Users\Desktop\A\[MSFGS.xlsx]GSD'!F3:F93,">="&"1-11-2016", 'C:\Users\Desktop\A\[MSFGS.xlsx]GSD'!F3:F93,"<="&"30-11-2016")

    But it returns a value when MSFGS is opened. Any error in the formula?

    Thank you.

  49. Amit says:

    I have one worksheet which have external reference. I received this excel via email. When I click on cell formula shows as below

    '\\s00697\team\Property Department\High Value Homeowners\Underwriting Guides & Rates\XXXX RATERS\[BGS HomeGuard Rate Sheet v2.01 0916.xlsx]Earthquake rates'

    But I am not able to find BGS HomeGuard Rate Sheet v2.01 0916.xlsx.

    Please help.

    Thanks in advance,
    Amit Chavda

  50. Yan says:

    Hi i have define a few formula from sone external workbook. Is there any quick button or shortcut key for me to edit link of these external workbook to other external workbook?

  51. Bruno says:


    I'm trying to copy cells containing formulas into a new workbook, but I'm getting this error.

    Formula copied from August Monthly Management Report_draft v2.33.xlsm

    =IF(INDEX('EM raw'!$L3:$L18;sbrValue_EM_02)="";#N/A;INDEX('EM raw'!L3:L18;sbrValue_EM_02))

    What gets pasted:

    =IF(INDEX('[August Monthly Management Report_draft v2.33.xlsm]EM raw'!#REF!;sbrValue_EM_02)="";#N/A;INDEX('[August Monthly Management Report_draft v2.33.xlsm]EM raw'!#REF!;sbrValue_EM_02))

    Any ideas?

  52. Kishore says:


    I want to use some values from the sheet1 in a purchase into another workbook's newsheet.


    This works but if i change the values in sheet1(source sheet) means value in the newsheet which values are extracted from sheet1 also getting changed. And i want to make it unchangeable in the new sheet.
    Is there any possibilities for that?

    Please help!

    Thanks in advance

  53. Mike Price says:

    In fact I could use the same sheet and put them in difference columns but I would still need it to choose the correct column depending upon whether the number typed into the cell has a prefix of E or D

  54. Mike Price says:


    Asking the question is probably more difficult than the answer, but here goes,
    I am currently using this formula =LOOKUP(J4,'PIT Codes'!$A$2:$A$201,'PIT Codes'!$B$2:$B$201)
    The user inputs a number and this references the sheet 'PIT Codes'to populate the sell with the corresponding code.
    Unfortunately we have had to introduce another size of PIT so I was wondering if I can make it check one of 2 sheets 'PIT Codes E' and 'PIT Codes D' the difference will be that the user inputs E1 or D1 depending which they have used.

  55. Jacque says:

    Is there a shortcut for referring to multiple sheets? Like, if I have a table to summarize a year's worth of data and I want to input the data in cell A1 from Sheets Jan, Feb, Mar... is there a way to do that easily? Like when you drag the square in the lower right of the cell it automatically copies the adjacent cells from the other sheet (unless you lock the first cell, in which case it copies the same cell). Is there a way to do that in multiple sheets instead of multiple cells? Does that make sense? It's so tedious to have to keep changing the Sheet name in the formula to refer to other sheets.. :(


  56. Filippo says:


    if a I have e sheet(01) with a row(row1) with formulas referring to another sheet(02) and I copy sheet(02) to another sheet(03) changing some datas (typically invoices) if I want to create another row(row2) in sheet(01) referring to sheet (03) it doesn't work, excel changes the cell referred and more important can I automatically say to the program to change all the formulas from the one I wrote manually for the sheet (02) to the ones of sheet (03) or do I need to do it manually for each cell?

  57. Jane Kelly says:


    May I know about the serial number of first sheet reference to next sheet to 1,5,11,30,45 like that.How to use what formula.


  58. Majid says:

    is there a way to automatically reference a different sheet's name for example A1 = sheet1,


    hence it would look at cell B1 in sheet1

    so I'm trying to reference another sheet by getting its name from a cell rather than typing it

    thank you

  59. Luis says:

    Hi Svetlana,

    I have come across various of your articles in the last few days and found them all quite helpful, so thanks a lot for your effort and work :D

    Now, the problem that brought me to this specific article is whether an Excel workbook can work "as an interface" to another Excel workbook. I mean, one Excel file contains both some important and "basic" information along with instructions as to how use this data, but I want that Excel to remain "hidden" and only use its contents (both the information and the logics) in another Excel file.
    From what I have read in this article, I would say that the information can be brought easily, but a doubt remains in my mind as to whether I should be able to bring the logics as well...

    (I would really try to pull this off myself but unfortunately I am far from being an Excel expert, I am not even a medium-level user to be honest, but there is work to be done...)

    Thanks again for everything,

  60. Sue says:

    I read over the posts. I hope that I didn't overlook the exact answer to my question.

    I have a workbook with several Data sheets and 1 Totals sheet. Each data sheet has rows that contain updating totals as new data is entered, using a formula. These new totals would be in the same column but different rows. I would like the updated totals from each Data sheet to record on the Totals sheet in a specific cell. Therefore, I would need to add something to the end of the formula in the Data Sheets, that, after it calculates the new total, it also places that number in the Total sheet.
    Your help is greatly appreciated.
    Thank you.

  61. Michael says:

    Dear Svetlana,

    Thank you so much for your really useful post. Very clearly written too!

    I've successfully created links to other workbooks now but I would like the formatting of the original data to be carried through too and I'm really struggling with that. For example, if a colleague has categorised some data as 'category red' and highlighted the cell accordingly in their sheet (the one that my central sheet is linking to), the data come through but not formatting.

    Any help would be hugely appreciated!

    Best wishes,

  62. H.Kin says:


    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?

  63. Jay says:


    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

  64. marc says:

    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.


  65. Antonio says:

    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?

  66. Upin J Dattani says:

    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.

  67. Jim H says:

    re: Referencing between sheets in the same workbook:

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

  68. Heather Pacheco says:

    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?

  69. Heather Pacheco says:

    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?

  70. Amiya says:

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


  71. Shiferaw says:

    I have worksheet sheet1,sheet2 and sheet3 with big data. this sheets were added together in "summary sheet"
    now I added another sheet4 with the same rows and columns as "summary sheet" and want to sum up on the Summary sheet as
    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.

  72. Shiferaw says:

    I have worksheet sheet1,sheet2 and sheet3 with big data. this sheets were added together in "summary sheet"
    now I added another sheet4 with the same rows and columns as "summary sheet" and want to sum up on the Summary sheet as
    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.

  73. ck says:


    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?

  74. Aru says:

    I want to refer a closed worksheet cell.


    How to put filename with using formula?

  75. Aftab says:


    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?


  76. Yvonne says:

    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

  77. Az says:

    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.

  78. jeff says:

    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.

  79. Darryl says:

    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.

  80. Kelly says:


    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

    • Lance says:


      Please try using the nested function

      For example put your variables in A1 and A2
      =INDIRECT(CONCATENATE("'\\2016 Sheets\",A1,A2,

  81. Carlos says:

    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?

    • Christopher Smith says:

      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

  82. Kaushik says:

    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.

  83. Ginalyn Grosz says:

    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?

  84. yonatan says:

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

  85. Billie says:

    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!

  86. George says:

    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.

  87. Spencer says:

    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!

  88. Anna says:

    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?


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

      • sam says:

        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 if i select any name created in a dropdown box, it should show all the values related to that name in another page.

  89. Ramki says:

    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.


  90. Felipe says:

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

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


      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.

      • Felipe says:

        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!

          • Felipe says:

            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!


            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.

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


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

              • antoine says:

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

    • David says:

      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.

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