Comments on: How to use INDIRECT function in Excel - formula examples

This Excel INDIRECT tutorial explains the function's syntax, basic uses and provides a number of formula examples that demonstrate how to use INDIRECT in Excel. Continue reading →

Comments page 3. Total comments: 215

  1. Hi there, I think I understand how the behavior works for R3C. It is not referring to the same column (in your explanation above, the arrow pointed to column C), it is actually referring to the same column where the Indirect formula located (in this case, column D).

    If you try to put "Test" in D2 and update the text in C5 from R3C to R2C, cell D5 will appear "Test" (Indirect formula is referring to the same column but row 2, which has a "Test" now)

    1. Hi Shau Leong,

      From all appearances, you are right. Thank you for explaining this case, I will update the example.

  2. I was looking to do this while summing across multiple tabs. The normal formula is

    =SUM('assets 1:end 2'!$F$4)

    I've tried the following but it isnt working could you assist?

    =SUM(INDIRECT("'"&C$75&":"&$P$7&"'!"&$C$74&4))

    C$75 = assets 1
    $P$7 = end 2
    $C$74 = F

  3. Hi,

    I'm trying to sum the same cells (C9,C10....C80) from multiple sheets onto a 'totals' sheets, but the data is on-going so I want to be able to update it to include a new sheet each month.

    If it were static this is what I would do:
    =SUM(July:December!C9)
    and it would be great.

    However I want 'December' to be dynamic so that it is possible to just change one cell from 'December' to 'January', and have C9...C80 all update automatically based on a formula.

    I was trying this

    =SUM(JULY:INDIRECT("'"&$F$2&"'!"&G2))

    where F2 is the new month, and G2 is the text "C9", however I get the error #NAME? returned, and in the formula builder it says "Volatile".

    Any suggestions?

    Thanks

    1. Hello!
      The 3D link cannot be created using the INDIRECT function.
      If you want to sum the same cell from all sheets except the current one, just enter a formula like this:

      =SUM('*'!G2)

      The sign * serves as a wildcard, which is interpreted as "all sheets except this one". When you press Enter after entering this formula, Excel converts the formula to use the actual sheet names.

      1. @Liam
        It is in fact possible to do what you want, but I don 't know if you are interested after such a long time.

        1. I would like an answer to this if you have it.

  4. I have the following on Sheet1:
    =SUMIFS(test!C3:C156,test!A3:A156,($B3))

    I am trying to set the upper range of the search, 156, in cell Sheet1 A1. I tried the following:

    =SUMIFS(test!C3:C&(=indirect($A1)),test!A3:A&(=indirect($A1)),($B3))

    What is the correct syntax?

    1. Hello!
      In the SUMIFS function, you cannot specify a formula instead of a range.
      Use the SUMPRODUCT function:

      =SUMPRODUCT(INDIRECT("C3:C"&$A$1),--(INDIRECT("A3:A"&$A$1)=$B$3))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  5. I was really helped out by just going through the samples on this page.
    My workbook consisted of several sheets, and I was impressed of the flexibility of the INDIRECT function combining information between them, all due to your thorough details.
    This made me able to dynamically update formulas in the relevant cells.
    I highly recommend this page !!!

  6. Hello, is there an alternative to indirect command to make a dynamic reference to another workbook without needing that other workbook open, likea direct reference formula does? I'm trying to make a spreadsheet that will gather data from 30 other spreadsheets, so I'd like it to work without needing 30 spreadsheets open.

    Thank you!!

  7. Hi

    I have any issue that when I am using the following formula and when I insert a row if the spread sheet 2 call People (same name as H4 in the master sheet), then the reference should change from !B12 to !B13 but it did not change.

    =INDIRECT("'" & H4 & "'!B12")

    Could anyone able to help me out with this.

    Thanks

      1. I have the same question here, and dont know if this possible.

        Is there a way to make the cell name recognize as just text the B12 in Franks first formula, and be able to copy that formula down. So when i pull down the formula it would allow me to reference a different cell on the other worksheet.

        I want the H4 refence to stay the same (and I have that done), but I want the cell number (the B12) to change for the referenced sheet, not looking at the current one.

        1. Hello!
          You can make the row number change as the formula is copied down the column by replacing it with the ROW function.

          =INDIRECT("'" & H4 & "'!B"&ROW(A12))

          Pay attention to the following paragraph of the article above - INDIRECT with other functions (ROW, ADDRESS, VLOOKUP).

        2. Easier way to ask. Can I make the Range dynamic, to reference another cell another worksheet.

  8. Hi! I'm trying to create a two-level drop down list for more than 500 cells and needs to use the indirect function. How can I make this happen without doing it one by one? Is there any formula I can use? Thanks! :)

  9. Very very useful. It help so much, thank you!

  10. Extremely helpful page!! Many many thanks!!

  11. Dear Sir,
    I have a problem using QUOTE function with INDIRECT function.
    The QUOTE function format is =quote("146.1.JYC.ASX","Last")
    I break up the function into 3 texts in 3 cells.
    D1 => quote("146.1.
    D2 => JYC
    D3 => .ASX","Last")
    CONCATENATE function: concatenate(D1, D2, D3) will looks like the quote("146.1.JYC.ASX","Last").
    If I apply INDIRECT function with the CONCATENATE function, and the result is "#REF".
    Can you help to solve my problem because D2 will change from time to time.
    Thanks for your early reply.
    Edward Wong

    1. Hello!
      I would like to learn more from you about the "QUOTE function".
      You wrote: "If I apply INDIRECT function with the CONCATENATE function, and the result is“ #REF ”." What formula did you use?
      What result would you like to get?

      1. Hi Alexander,
        Thank you for you interesting in this matter.
        Finally, I figure it out. It is not relate to INDIRECT function.
        My solution as follow:
        Put JYC in Cell D2
        Put the QUOTE formula =QUOTE(CONCATENATE("146.1.",D2,".ASX"),"Last") in cell F1 hence get $1.1.05 as a result.
        If you change D2 to APA and get $11.34
        That is what I want to do.
        Thanks you once again.

      2. Hi Alexander,
        Thank you for you interesting in this.
        To make use of QUOTE function, you must have Excel QUOTES add-in (https://gaiersoftware.com/Excel/) to MS Office.
        And quote("146.1.JYC.ASX","Last") will get the latest price of the stock "Joyce Corporation Ltd" from Australia stock market.
        I put the formula =quote("146.1.JYC.ASX","Last") to cell F1 and get $1.15 at the moment.
        So, I think you will understand what I expect by using CONCATENATE function with QUOTE function to achieve another stock latest price as D2 change.

  12. I want how to get the excel sheet 1 cells in sheet 2 what is the formula in that

  13. I want to extract a value from a different workbook (containing multiple sheets named - state 1 , state 2) such that "state" is fixed and i will input value 1/2 each time in a cell and based on it excel should extract value from state 1 or state 2 sheet. how can i frame the indirect formula? (keeping in mind that fixed "state" string needs to be concatenated with 1/2 input cell. Also state 1 or state 2 has spaces in between)

  14. G19=id dump 10th dec"19 H19=dump

    Above are file and sheet name (dump) respectively
    Index(INDIRECT("'["&G19&"]"&H19&"'!"&$E:$F)
    However within index it is showing error

  15. Thank you for the info it is very useful
    I'm using the following formula to get the count of the number 150 from a worksheet without an issue,
    =SUMPRODUCT(COUNTIF(INDIRECT("'My Sheet 1"&"'!A1:L300"),150))
    For some reason i can not figure out the proper syntax to use across multiple worksheets to reference or example I want to count the number 150 across 3 Sheets that are not next to each other. How can i syntax it lets say they are named
    My Sheet 1, My Sheet 2, My Sheet 4

  16. When I use this formula it works for the first cell, but when I drag it down 20 rows it does not work. It will change the sheet reference (A2, A3, A4), but it populates the result from A2 (the starting point). If I manually change A3 to A3 or A4 to A4 in the dragged down formula ....then it will give me the proper result. For example:

    =INDIRECT("'" & A2 & "'!B12") ....correct result is 2932
    =INDIRECT("'" & A3 & "'!B12") ....correct result is 200 but excel gives me 2932. when dragged A2 changed to A3 but still get A2 result.
    =INDIRECT("'" & A4 & "'!B12") ....correct result is 1500 but excel gives me 2932. when dragged A3 changed to A4 but still get A2 result.

    Can someone explain what I am doing wrong?
    thanks
    Dee

    1. Hi

      Try to change the formula to: =INDIRECT("'" & A2 & "'!B:B")

    2. I am also having this issue and can't seem to find an answer.

    3. I have this same issue; did you get it resolved?

  17. how to give dynamic range every time to get sum in excel sheet

  18. I am trying to sum multiple spreadsheets by using the tab name to identify the start and end sheets. But I keep getting a "ref" error. Any thoughts?
    =SUM(INDIRECT("'" & CONCATENATE(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))," Start:",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))," End'!",ADDRESS(ROW(),COLUMN()),")")))

  19. Hey,

    Is there a way to use indirect in multiple columns in the same sheet?
    I have made a sheet (sheet A) listing info from other sheets (B,C,D).
    If I want to add sheet E, I now have to also add a row in Sheet A which will refer to sheet E, in this new row I pull through the formulas from the row above.
    I don't want to have to change the formulas in every column to fit with the correct sheetname. With the indirect formula that works, only so for one column.

    I'd attach an example file, but don't see how to.

    Looking forward to your reply,
    Kind regards,
    Ben

    1. Some more deduced clearer info.
      The cell wrongly displaying refers to a cell in which dependant validation is present.
      The cell I refer to on the sheet I refer to with the indirect formula, the content is depending on the value of an other cell, I use the indirect formula to make a dependant validation. Which creates the problem.
      Any tips on a workaround?
      I do need the dependant validation as much as the indirect reference
      Made a screenshot with some info, find it here:
      https://drive.google.com/file/d/1QPSHrXqPG7dNJD6XGy9ZOG73mLaHrirH/view?usp=sharing
      regards,
      Ben

  20. Best wishes; from everyone of us.

  21. I am having an issue getting something to work properly.

    I have a workbook that contains a list of Websites, and under each website are the names of the users that are considered experts with those websites. Rather than having people have to search through to find an expert they want to ask questions of, I created a new sheet that includes a drop down list of the websites.

    When a user clicks on the drop down list (which is in C2), and selects a website, I am wanting to populate that list in in column F, starting at F2.

    I have the basics of it working fine. The issue I am having is that each website has a different number of 'experts'. Some only have one, and some have over fifteen.

    My issue is that (unless I am missing something), I needed to place the formula in all 20 cells, so that all the experts would fit. Now, when a website is selected that has less than the maximum number of experts, the remaining cells show #N/A.

    I have tried using an =IFERROR to get rid of the #N/A, but am not having any luck.

    Do you have any suggestions?

    Here are the basics of the code:

    The website drop down list is done with Data Validation, using the named range: =Websites

    For the experts list, I have used: =INDIRECT($C$2)

    When that came up with the extra #N/A's, I change it to: =IFERROR(INDIRECT($C$2),"")

    Unfortunately, that did not get rid of the #N/A's.

    The list looks similar to this:

    John Smith
    Fred Jones
    Steve Major
    Rod Hawke
    #N/A
    #N/A
    #N/A

    Please help!

    Thank you,
    Jason

  22. Hi, I have a cell A4 that has another cell's address in it. So cell A4 reads $D$2, it is a dynamic value. How do I use the offset function to reference this cell? I want it to reference the dynamic cell address rather than the cell A4, which contains this information.

    Thanks!

  23. EXCELLENT - THANK YOU - works like a dream.

    THANK YOU

  24. Can you use INDIRECT fron one sheet to another sheet?

  25. Great post, nice examples, you save me a lot of time. Thanks!

  26. Good post, really useful. However it doesn't fix the problem for my unfortunately :(
    I need a formula in which I can update the links every month easily. However, I don't get the indirect function to work :(. For example, the data that needs to be updated monthly is linkt to follow directionary for januari 2017

    =('M:\SSC\Vestigingsmanagement\Mebin\2017\01 2017\Rapportages\[amsterdam totaal KER Template 2017.xlsx]Samenvatting'!$E$113)

    Februari 2017 is linked to:=('M:\SSC\Vestigingsmanagement\Mebin\2017\02 2017\Rapportages\[amsterdam totaal KER Template 2017.xlsx]Samenvatting'!$E$113)

    The only thing that changed is 01 to 02. So my idea to make a template, in which I can select 01/02/03 depending on what month I need, that the links automatically changed with it. Is there a way to do that? The search and change function(ctrl+F) is not an option, since it is not very accurate!

    I hope somebody can help me! Thanks in advance!

  27. Very very helpful, thank you!

  28. Really good post! Excellent examples. Very helpful. I might use something similar even in VBA. Thank you and Merry Christmas :)

  29. Hello
    I am trying to create a dependent drop down list using the following formula =INDIRECT(SUBSTITUTE(F2," ","_"))
    However, I get the #REF! error

  30. One of your initial examples has a mistake in the description.

    The formula =INDIRECT(C5,FALSE) is in cell D5. So, the formula grabs the value in C5 (R3C) and turns that into the cell reference D3, not C3, because the formula is in column D.

    There is no double indirection. The blue arrow in your diagram from cell C5 should really point to cell D3, because the INDIRECT formula is in Column D.

    You can easily test this by changing the value in D3. D5 changes as well.

    1. By the way, this also explains the confusion you describe here:
      In all honesty, I failed to come up with
      any plausible explanation of such behavior,
      maybe it's just a quirk of Excel INDIRECT.

  31. Good morning,

    I have a workbook with lots of basic formulas just like ='Cost Detail'!A1
    I would like to change the 'Cost Detail'! part by an INDIRECT function.
    I would like to do so by "search and replace". But the A1 part should not change inside the formula. I cannot find a way to do it without rewriting each one of my 400 formulas. Can you help me?

  32. is it possible to combine indirect and index? or is it impossible without VBA to cell values in closed workbooks?

    eg. =index('c:\temp\[filename.xlsx]sheet1'!A2,1,1) gives the value of a non open excel file..

    so if i have the different part in cells
    a1: C:\temp\
    a2: filename.xlsx
    a3:sheet1
    a4: a2

    the formula ="'"&A1&"["&A2&"]"&A3&"'!"&A4 gives the "correct" string.. I could copy this and parse it as value (and then edit the cell and enter)..

  33. In one workbook I have two worksheets: Analysis and Items.Items contains detail line items sold with a customer ID in column N. There may be one or many rows with the same customer ID. In Analysis WS I want a count of when the customer ID changes in Items WS(counting number of customers).Using Countifs function as there are multiple criteria. Want to compare value of N to N of previous row. Can you help? Tried Index and Offset, but no luck.

  34. Hi ,
    SOS. I have non contiguous cells on one sheet ( different sheets) and i want to sum or average them using indirect function. How does it work ? Many thanks.

    1. Hi JM Badi,
      As you might have noticed in the examples, AVERAGE and SUM functions are used with INDIRECT when it is referred to a cell with already named range. So you will most likely have to create a long INDIRECT formula, creating additional cell references for your non contiguous cells from different sheets.
      You can find it explained in more details here.

  35. I'm having trouble getting "Creating an Excel dynamic reference to another workbook" to work. I type this in verbatim - nothing but REF#.

    I understand the big picture regarding how it works. I also have the source sheet open. I tried closing and re-opening the source workbook again, no good.

    Any assistance would be deeply welcomed and appreciated.

    1. Nevermind, I figured it out. I needed to type the following: =INDIRECT("'["&$A$2&".xlsx]"&$B$2&"'!"&C2)

  36. I am using the INDIRECT function to pull data from another tab in the current worksheet. When I edit the location I get the REF error. When I cut and paste the same text it works?

  37. =COUNTIF(D6:D31,1,("1=Trivandrum"),("2=Kollam"),("3=Pathanamthitta")

  38. =COUNTIF(D6:D31,1,("1=Trivandrum"),("2=Kollam"),("3=Pathanamthitta")
    please correct this

  39. Hi Svetlana Cheusheva

    A1 jhon
    A2 krish
    A3 robert

    B1 50
    B2 100
    B3 150

    now i need formula for

    Column D jhon 50
    Column E krish 100
    Column F robert 150
    Auto insert value of persons

  40. you can comple this please, and explain my more , indirect(adresse(count())) help do it.

  41. Hello,
    Not sure if something changed from the time this was posted but I found a bug in your syntax for the sheet reference example on my system.

    You listed the following syntax INDIRECT("'" & $A$1 & "'!" & B1)
    But that yields #REF error until the last quote is moved right of the cell address "B1". At least from my test on Office2016 your example fails until I do this...

    INDIRECT("'" & $A$1 & "'!" & B1) BROKEN
    INDIRECT("'" & $A$1 & "'!B1") WORKING

  42. Hi Svetlana,

    I was so intrigue by your article, would it be possible to send me an example sheet so that I could download it and use it in my work.

    Thank so much, Edward

    How to use INDIRECT function in Excel - formula examples

  43. Please help to me,
    1,2,3,4,5,6,7,8,9,10,...etc have magic number and models in customer list and I want to reference like that
    10,21,32,34,35,45,46,48,67,56,43,... Please assists to me...

  44. =IFERROR(HLOOKUP($B$7,INDIRECT("'["&$C$1&"]"&$A$3&"'!"&$C$18:$CV$55),38,FALSE),"")

    I'm trying to pull data from another workbook on our SharePoint site.

    C1 = "http://blueshare/Departments/Finance/Budget" and Rates/Billing/Generation Partners Workpapers/2016/09 - Generation Partners Workbooks September 2016/Residential Worksheet September 2016.xlsx
    A3 = Data Input Worksheet

    I'm typing in the range: C18:CV55.

    Please tell me why it's not working. Thanks.

    1. It looks like you are putting the entire path in square brackets. Shouldn't just the file be in square brackets?

      eg https://longpath/%5Bworksheet%5D

      And because there are spaces in the filepath you might need to add single quotes around the whole file name. Have a look at the Creating an Excel dynamic reference to another workbook for the correct syntax.

  45. Good day and thank you for helping all of us!
    I have an issue and I'd be so thankful if you could help me.
    I have a spreadsheet (daily data) that pulls data from a closed workbook.
    I have a folder (auto) with data from every day as a different workbook. Nov 09.xlsx, Nov 10.xlsx, Nov 11.xlsx, etc.

    in my example, the data is shown in cell V21 in my 'daily data' spreadsheet. the formula for V21 is ='I:\DOD\Intervals\auto\[Nov 09.xlsx]Daily DOD'!$F21

    Now, what I need is the ability to use another cell in my 'daily data' spreadsheet, where I type the date I need the formula in V21 to pull data from.

    so I want to use cell A1 to type a date (Nov 09 in this case) and have cell V21 go to 'I:\DOD\Intervals\auto\[Nov 09.xlsx]Daily DOD'!$F21 and give me that data. but if I change the date in A1 to, say Dec 10, I want cell V21 to give me the data in 'I:\DOD\Intervals\auto\[Dec 10.xlsx]Daily DOD'!$F21 instead.
    Is there a way to concatenate the date in A1 to the formula in V21? or is there other function/ formula I could use to automate this process, instead if changing the formula in V21 manually every time I change the date I want to see?

    Thanks so much!

  46. I need help with an indirect formula. I have drop-down two columns, say A and B. If the choice "Pres" "Corres" or "Other" is selected in column A, I want column B to display the relevant drop-down listing.

    My indirect formula works exceptional. However, the problem I'm having is that when I enter "Other" I want the cell in Column B to able to input my own information. And because I don't have any info in the drop-down for "Other" it's coming up blank.

    Is there a way for the dropdown to occur for only "Pres" and "Corres" only and allow text for "Other"?

  47. Hi Svetlana Cheusheva

    I need help in the following data. on the basis of multiple conditions I want to put a Text value (Result). Like as per Voucher Dt., CHQ Dt., CF Report and Status the text value should come accordingly.

    suppose if voucher dt. (Specific month dt) + chq dt.(Specific month dt) + CF Report (Specific condition) + Status (Specific Status Name) then Result (Text Name) should come on the basis of all specific conditions.

    Voucher Dt. CHQ. Dt. Name Amt CF Report Status Result
    15-08-16 15-08-16 ABC 500.00 Chq in Hand Received CDC
    20-06-16 05-08-16 DEF 400.00 Chq in Hand Not Received PDC
    21-06-16 05-08-16 GHI 500.00 Factoring Received DISCOUNT CHQ
    25-08-16 25-08-16 JKL 300.00 Cash in Hand Received CASH
    06-08-16 06-08-16 MNO 400.00 Chq in Hand Not Received CDC

  48. Hi,
    I'm trying to combine INDIRECT with a 3D consolidation, doesn't seem to be working:
    Sheet: A, B and C
    each have :
    A1 = 1 for sheet A
    A1 = 2 for sheet B
    A1 = 3 for sheet C
    if I do:
    =sum(A:C!A1) then I get: 6

    but if I do: =sum(indirect(A:C!A1))
    then I get an error...

    Any idea ?
    Thank you for your help

  49. explain the formula =iferror(index(dddd,small(if(dddd"",row(indirect("1:"&rows(dddd)))),row(indirect("1:"&rows(dddd)))),"")

  50. I'm using the following formula and when i insert a row in the source worksheet, the cell reference should change to F57 but it does not.

    (INDIRECT("'"&A2&"'"&"!" &"F56")

    Please help.

    1. It doesn't change because "F56" is interpreted as text when placed between the quotes"" not as a cell reference. Thus changes to rows columns do not impact it.

      You will see the A2 shift because it is placed as a cell ref between the &&. I am battling a similar problem where I want the "F56" to increment but it wont. In the example in the article the author lists the following syntax (INDIRECT("'"&A2&"'"&"!"&F56). This would allow F56 to increment but it is fundamentally incorrect because it is trying to place the contents of F56 on the current sheet into the string rather than appending just the letter F and number 56 to the string for later lookup in the proper sheet. Let me know if you find the answer.

      1. Any fix on this one yet?

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