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

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

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

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

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

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

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

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

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

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

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

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

  12. Hi!

    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?

  13. Can u help me with this.
    =if(B2="","",vlookup(B2,'iw39',A:B,2,)),If(C2="","",vlookup(C2,'iw29',A:H,5,0))
    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"

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

  15. how to auto calculate frieght total at end of the month by making daily transaction sheet to ledger sheet auto

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

    THanks!

  17. Hi,

    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.

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

  19. Hi,

    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.

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

    Thanks

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

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

    Thanks,

    Dorian

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

    Thanks
    Vasya

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

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

  26. Hey gang...If a cell is desired to be empty on sheet 1, how can I not have a zero populated on sheet 2?

    1. Wally this is what I used

      ='SHEETNAME'!CELLNUMBER&""

    2. I am having the same issue.

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

  28. Hi,

    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?

  29. Hi,

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

    =[Purchase.xlsx]Sheet1!$A$5

    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

    1. Hello Kishore,

      In this case, replace the formulas with their values to break the connection between the extracted and original values. One of many possible ways to do this is Paste Special > Values (the detailed steps can be found here: How to copy values in Excel.

  30. In case anyone else has a similar needs, I sorted this out by using Vlookup and define name for the range.

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

  32. Hi,

    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.

  33. 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.. :(

    Thanks!

  34. Hi,

    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?

  35. Hi,

    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.

    Thanks,

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

    vlookup(A1!B1,..)

    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

  37. 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,
    Luis.

  38. Hi,
    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.

  39. Hi,
    Is there a way to reference a cell/cell range from a OneDrive Excel file?
    Thanks for your help.
    Regards,
    EE.

  40. 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,
    Michael

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

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

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

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

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

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

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

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

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

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

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