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

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

Comments page 2. Total comments: 361

  1. Hello, I'm trying to create a master spreadsheet which is compiled of different sections of data from different people. So for example Person A has a section which they can update and feeds automatically into the master spreadsheet, as can Person B for a different section. Each person can only see their own section but those with the master spreadsheet can see it all, is this possible?

  2. Hi!

    I need to take figures from one spreadsheet and figures from another spreadsheet and put them together on another spreadsheet. The problem is that I am doing monthly work and at the end of the month I have a few days that need to be added into the first week of the next month on another report. So I have a spreadsheet that takes the number automatically. But I can't get the 1st weeks numbers in because I can not figure out how to get the end of the month number from the month before and the current end of month numbers to merge together for the first week. I keep reading everything here but there is no plain and simple way here on how to do something like that.

    Does anyone have an easy and simple trick to get both months number on one spreadsheet. I would really appreciated it. Because whoever did this before kept totals in a book. Pen and paper and I do not wish to do that....lol!!

    Thanks for any help anyone can give me!!!!

  3. Hi.
    I created a data extraction file that is using filter function to pull data from a master workbook. It has a "list" that can be filtered and generate a list referencing to the master sheet.
    I am trying to make the external reference work while the workbook is closed. The master and extraction files sit at the same path on sharepoint and I did put the exact path to the file: "https://location/location/location/file.xlsx". So the path would be 'https://location/location/file.xls!Table1

    When I have the master file open, the function it will only indicate the file name, but when I close the master, the entire file path shows. However, the filter and file exaction won't work. Any help would be appreciated so that the filtered file will work without the master file open.

    I am wondering if it's not working because it's on sharepoint.

    1. Here is what I have:

      =CHOOSECOLS(VSTACK('https://filelocation/filename.xlsx'!Table1[#Headers],SORT(FILTER('https://filelocation/filename.xlsx'!Table1[#Data], 'https://filelocation/filename.xlsx'!Table1[Filtercondition]=C2,""),{4,7,1},1)),6,8,7,4,1,2,18,19,20)

      1. I included the brackets [ ] for the file name. But still getting #REF error. For the cell reference, can it be a table for a closed book reference? Or does it have to specify the cells?

        =CHOOSECOLS(VSTACK('https://filelocation/[filename.xlsx]Sheet Name'!Table1[#Headers],SORT(FILTER(' https://filelocation/%5Bfilename.xlsx%5DSheet Name'!Table1[#Data], 'https://filelocation/[filename.xlsx]Sheet Name'!Table1 [Selected Column name]=C2,""),{4,7,1},1)),6,8,7,4,1,2,18,19,20)

        1. Hi Shanna,
          I would advise you to build the formula having both workbooks open and selecting the ranges and cells in the second workbook using the mouse. In this case, Excel will use the appropriate references automatically. After you close that second workbook, your external reference will get updated automatically to include the entire path to the file.

    2. Hi! Pay attention to the following paragraph of the article above: External reference to a closed workbook.

  4. I want to create an active spread sheet which will pick up values from different columns in the master sheet with a condition. Can anyone help.

    E.g. Column A in active spread sheet should pick up value from column G of master spread sheet with a condition that it picks up on the open tasks to the active sheet. How to do that.

  5. Hi

    Kindly assist with a function that will give me the counts from other sheet, I have sheet 1 which has Cell B1 and E1 where I select my criteria as dates then I have sheet2 where my data is. I want to get the counts of records in Sheet 2 which match the date range selected in Sheet 1 Cell B1 and E1. here is the functions that I tried
    =COUNTIFS(Sheet2!D3:D9,(Sheet2!C3:D9>=B1)*(SHeet2!C3:D9=B1")-COUNTIF(Sheet2!C3:D9,"="&B1)-COUNTIF(Sheet!D3:D9,"<="&E1)

    None of these are working for me

    Thank you

    1. Hi! Learn the syntax of the COUNTIFS function carefully. See the examples in this article: How to count cells with multiple criteria. Also note that COUNTIFS function arguments cannot be formulas. Also note that all cell ranges in a COUNTIFS formula must have the same size. Simultaneous use of C3:D9 and D3:D9 is not allowed.
      I can't recommend a formula to you as I can't see your data.

  6. Hello! Thank you for this. Do you know of a way to have just the formula linked to other workbooks? For instance, if I have a cell that calculates the sum of a list on one spreadsheet, is there a way to copy the formula to other spreadsheets such that, when I update the formula on the first (like, maybe i decide to divide the result by 2), the formula on the others also updates (so they also now divide the results of their sums by 2)?

    1. Hi! An Excel formula cannot automatically change if another formula of the same type changes. You can write the values that change in separate cells and make references to these cells from all formulas.

      1. that wouldn't work for what i'm trying to do, but thanks for the quick response! :)

  7. After I create my external links from multiple external workbooks into one workbook on a shared drive, I get a #Value error when the external workbooks are closed. When I open them, it will update on the main workbook. Can you please help with me the formula where the workbook will still show the values without the other workbooks being open. I used the =countif(range,"V") formula. I would like for it to continue to update and display the values without the other workbooks being open. Thank you

    1. Hi! Pay attention to the following paragraph of the article above: External reference to a closed workbook. I hope it’ll be helpful.

  8. Hi, I have a doubt on these references,
    Consider I have 2 working excel files. One is Master data file (excel_1) and the another excel file which I am working on currently (excel_2) where I took references from (excel_1) file for some data. Now I have closed both the files.

    Now, When I 1st open my current working excel file (excel_2), everything is working fine. But when I open the Master data file (excel_1), all the cells in the excel_2 which has references from excel_1 has turned into #N/A errors. How could I solve this type of issue? I need to get dynamic values from my Master data always (excel_1) into excel_2.

    Thanks

    1. Hi! Unfortunately, I have not been able to reproduce your problem. I don't know what actions you performed with the file and which formulas return the error.

  9. Hi

    I'd like to create an Excel template with the ability to attach items from another Excel file. For example, a serial number is entered into the template and all the information for this item (price, description, image, etc.) is linked from another Excel file.

  10. Hi,

    I'm trying to set up a spreadsheet of information that has multiple sheets. Sheets 1 and 2 will always be the same. Sheet 3 will be a 'review' spreadsheet. There may be multiple 'review' sheets that I need to insert weekly (an indefinite number), and so want to create a template that I can copy and pull into place. Is there a way I can set a formula to autopull information through from a cell on the previous sheet to the left hand side when I don't know what that sheet will be called?

  11. Hello ! I have a workbook with two sheets. In the first sheet (DAY TIME) I get values from the second sheet (GET VALUES) using =INDEX(GET_DATA!$M$5:$M$1004;MATCH('DAY TIME'!$G$6;GET_DATA!$L$5:$L$1004;1))
    This extends from M to AU.
    I want to dynamically change the range of my search by just changing the values of two cells.
    Meaning for example in sheet1, in cell B2 I will input the number 305 and in the cell B3 the number 519 and the formula will lookup in M305:M519 and L305:L519 of sheet 2.

    I am trying with INDIRECT but I can't seem to find a work around.

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

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

    Thanks,
    Rohan

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

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

  14. thanks for your support

  15. nice

  16. Very good

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

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

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

    I

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

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

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

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

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

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

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

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

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

    Thanks!

  20. I have an error with this code,

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

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

    Follow Up and Daily Report are names of sheets.

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

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

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

  22. Hi
    Let's say one workbook contains multiple worksheets and I have made a summary sheet which takes inputs from all the sheets in the workbook. Now, I have multiple workbooks for different companies. Format of each worksheet in all the workbooks are identical.
    How do I develop similar 'summary sheet' for all the workbooks, without doing it manually sheet by sheet?

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

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

  24. Nice

  25. I can't find the formula in which the (Registration Fee column) in the cell G20 (Sheet Apr-22) Sum of Value 1,92,420/- Amt automatically show in Cell C4 (Sheet SD).
    And same with Stamp Duty, SIC, PIBD Column.
    Means I want cell G20 (Sheet Apr-22) Value in Cell C4 (Sheet SD) with formula.

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

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

  27. Hi All,

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

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

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

  28. I have a worksheet that I inherited. The main "sheet" references another sheet with the same exact data. It matches the columns that could be used in the main sheet. What situation would benefit pulling data from a different sheet?

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

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

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

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

    Any thoughts on how to format this properly?

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

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

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

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

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

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

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

    Any suggestions?
    Thanks!

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

        Original on sheet1:

        AVERAGE(sheet2!AD6:AD16)

        New dynamic formula that worked and got the same result:

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

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

  32. Hi Alexander/Ablebits Team,

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

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

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

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

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

    Thanks!
    Charles

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

        1. Hi Charles,

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

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

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

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

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

  34. Hi,

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

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

    Both workbooks are stored on OneDrive in the same folder.

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

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

    Looking forward to your reply.

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

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

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

    Paul

  37. Question for some
    I have a spread use used daily and saved on every dales Fuel Sales December but I have to take figures for December and go to my folder create January 2022 Master and input these numbers and start Jan Fressh

    is the a formula i can put in January 2022 Master so December would auto carry to January 2022 Master

    Let me know

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

    1. Hi Gregg,

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

  39. Hi,

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

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

    Thanks.

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

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

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

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

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

      I hope my advice will help you solve your task.

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

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

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

        Thanks, Again!

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

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

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

  42. Hello Svetlana!

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

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

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

    Regards,
    - Sergey

    1. Hi Sergey,

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

  43. Hello,

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

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

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

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

  44. Hello!

    Thank you for sharing, but I'm curious, because I work with a lot data, how to take address from other cell to be a task , for example if I want to take data from worksheet name A in cell B5, then I can type = A!B5, but I will do this in many times, so I think I can create colom wich fill with A (in cell A5) A A A A then I make another collom with B5 (in cell B27) B6 B7, then I don't want to type it one by one but want to drag it so it will be automatically take data, do u know how to do that, so I just type =A5!B27 , then the excel with take data from sheet A cell B5, but I can't just type that, would you know the formula? Thank you

      1. hello, sorry my question is not clear, so here it is, I want to retrieve data from another worksheet, if typed manually it will take a long time because the data is a lot, so I want to ask if the =worksheetname!cellname command can be written by clicking on a cell another, so there is already another cell containing the worksheet and cellname in a different cell, so I made 2 columns, the first column contains the worksheet name, the second column contains the cellname name, if I can fill in the command to retrieve data by clicking on the cell in the worksheet name column and the cell in the cell name column, I don't need to write one bye one "address of the data to be retrieved but only need to drag it down. . is this possible? so the =worksheetname!cellname command is filled from 2 other cells.

        1. Hello Susanto,
          You may have better luck using a lookup (my favorite is the INDEX-MATCH method).
          But if you really need it as you described - one possible solution for you may be the INDIRECT() function. So if your addresses are listed in column A, you can write the following formula in B1, and drag (or copy) it down: =INDIRECT(A1)

          Regards,
          Sergey

  45. I have a several excel files in a folder. Each file has the same 1st sheet named movement. I want to list each filename in a column and next to it the value from cell B79 in the movement sheet from each corresponding file. How can i do this without opening each file.

  46. I have several sheets within a file. Each sheet has a formula to get data from the 'INFO' sheet. =INFO!I1
    When the file gets sent to others and then the sheets are edited by different people and then all the sheets are copied back into the master file, this formula is messed up as it is trying to reference a sheet from a different file. How can I make this formula always pick the data from the 'INFO' sheet within this same file rather than going looking for an external file?
    ='[Sub Inspection Forms_R12_20210816.xlsx]INFO'!I1

  47. Hi,

    I have a workbook but some of the data is sensitive, whereas a lot of it is very useful for another department in my company. Is there a way to reference the relevant data in workbook 1 to a (supposed) workbook 2 and to have it update whenever we change data in workbook 1 without allowing file access to workbook 1?

    Workbook 1 is saved in a SharePoint Library that only my department has access to for confidentiality reasons, and we cannot allow access to this file to anyone outside my department but we really don't want to duplicate work by copying and pasting data that already exists for our other department.

    I hope this makes sense!

    Thanks in advance.

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

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

    Do you need more information?

    Thanks!

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

  49. IF functions between workbooks

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

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

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

    Can anyone identify what is wrong?
    Thanks
    Chris

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

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

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

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

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

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

        Thanks

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