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 2. Total comments: 113

  1. Hi there,
    I am using =FILTER very successfully but the "include" criteria I want to use, extend into an enormous list because of a variety of different choices I have set up to make a large number of different filters available to my users, with the result of the filter always appearing in the same space. So I want to "break down" the =FILTER formula I am using by referring to various lists of "include criteria" which I will store in different cells. I have been embedding SWITCH and INDIRECT functions into my =FILTER formula but always end up with a #VALUE! error.
    Is there any way to make "include criteria" dynamic rather than having to boringly spell them out in the =FILTER formula itself?
    So ... say I have ten different varieties of "include criteria" ... I have to make ten nested IF statements inside my =FILTER formula. It is really tedious.
    What I am doing is giving my users up to ten different "saved filters" so they can quickly flick between one filter and another and save their favourite ones etc. Sometimes, these saved filters use "AND" and sometimes they use "OR" and they all use up to ten different columns from my huge array of data to be filtered. The data is obviously kept in a different Tab.
    To save my =FILTER formula from growing into hundreds if not thousands of characters, all I want to do is to "import" "include criteria" from a series of different cells, which I can tuck away out of sight somewhere.
    Any ideas?
    Thank you so much if you are able to solve this one for me!!
    Kind regards - Jon

  2. Hi. I have used INDIRECT with dynamic references for years and I decided to upgrade from Excel 2013 to 2021. Now I get #REF! from all of my formulas using INDIRECT with dynamic references. I came across this article and found out why :-(

    I use the dynamic references to look up values from previous years without needing to enter the new year in the formulas every year and not needing to have the previous years workbook open.

    Is there a way to get values from other workbooks by using dynamic references without having to open them?

    Thanks.

    Roger Jacobsen

  3. Hello,

    Firstly, thank you for creating such a comprehensive article! Extremely helpful to many, I'm sure. Secondly, I don't think this specific post can help with my issue, but perhaps you can point me in the right direction?

    I have a template that I am creating (so formulas will need to be dynamic as rows will be added) where I'm trying to continue the row numbers while skipping headers. Here is an example of what it should be when complete:

    A B C
    1 data data
    2 data data
    3 data data
    Header
    4 data data
    5 data data
    6 data data
    Header
    7 data data

    The issue is that the template doesn't look like the example above, as the data isn't populated yet. The template looks like this:

    A B C
    formula data data
    Header
    formula data data
    Header
    formula data data

    How can I start numbering in A1, skipping headers and continuing numbering, without being able to tell Excel where the last number above Header 1 left off?

    1. Hello!
      Please try to enter the following formula in cell A2 and then copy it down along the column:

      =MAX($A$1:A1)+1

      Please check out this article to learn how to find the highest value.

  4. Hi there,

    My wife asked me to help her create an excel document for her work where in two columns I need dependent drop down lists.

    The main drop down (cell G3, only has single word data) and the first dependent list (H3, gives results of a couple of sentences) was easy. In a parameters sheet I grouped the specific data and gave it a name that corresponds to one of the four choices of cell G3. On the work sheet I used the =INDIRECT function in cell H3 (first dependent drop down)

    Now I've been struggling for a couple of days on getting the second dependent drop down (I3) to work. If I use the same function as in H3 then it returns the same options as in H3. When I try to direct it to the correct group name it doesn't work.

    The drop down options in H3 are all sentences (with spaces) and I think that's what causing the issue.

    Can anyone help/advice on how to get the right drop down data in I3 based on the main drop down (G3)
    I3 doesnt even need to depend on whats in the first dependent drop down list (H3) solely on whats in the main drop down list in G3.

    Thank you in advance!
    Rico

      1. Thanks Alexander. I will give it a try.

  5. I like how clear your explanation is, but I am trying to go in the opposite direction. I use VLOOKUP to get a cell reference to a location to some value (Current Data). I use the existing value from that cell, update the value, and then want to update the original location, making it Updated Data. I was able to use the indirect function to get the Current Data, but I can't find anything that Excel will let me do to indirectly store the data back in the original cell again. I have to do indirection, since I have many cells that have to be processed, and use a table to get the cell references to the data cells. This is basically a A = A+B problem. Any idea on how to stuff the data back into the cell again. Thanks.
    Tim Ryan

    1. Hello!
      Unfortunately, I could not understand your problem. Give an example of the values and cells you are searching and updating.

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

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

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

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

  10. 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. Easier way to ask. Can I make the Range dynamic, to reference another cell another worksheet.

  11. 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! :)

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

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

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

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

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

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

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

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

  20. I am trying to use Indirect to get text from a cell in another sheet
    =SUM(INDIRECT($A22&"!$B$3")) gets me the date ($B$3) that is in the sheet name in ($A22).
    When I try =SUM(INDIRECT($A22&"!$C$3")) which has text in ($C$3) it comes back with 0.
    Any ideas on how to get the text from that sheet to display. :)

    1. What do you expect the "sum" of "text" to be? I am not sure if I am more concerned that you are trying to sum a single cell or that you are trying to sum text. What is the cube root of banana?

      If you remove the sum function it will return the text.

  21. by using Concatenate function, how we further write in the resulted cell because resulted cell shows the formula nor text.

    1. Hello, Ali,

      For us to be able to help you better, please send the formula you use.

  22. Hi Svetlana,

    Is that possible to use indirect function to dynamically change the row number i.e. =INDIRECT($A$4&"!C31"), =INDIRECT($A$4&"!C32") and so on.

      1. Hello Taha,

        I get #ref! error when doing this. If I take the : out then it returns a value but does not increment when I try to cascade the formula. But even with the : in place it does not increment like the original question asked about.

  23. I have a small problem
    in cell A1 - birthdate 01/01/2010
    in cell B1 - =A1 : 01/01/2010
    when I try with indirect I get the number instead of a date format

    My goal is put in one cell indirect bithdate + birthplace
    A1 & " " & A2

    Thanks in advance.

    I hope you have a great holiday, and looking forward to your fresh look at this issue

    1. Hi, Kris,

      Try this:

      text(A1,"DD/MM/YYYY")&" "&A2

      If you want the date in American format, use "MM/DD/YYYY".

  24. Help! What is wrong with this formula?

    =(COUNTIFS('Prod - Resolved'!(INDIRECT("B3:B500")),"1 - Critical"))

    I'm trying to count the number of cells on the worksheet named Prod - Resolved, in the range B3 to B500, where the value is "1 - Critical".

    Thanks!

    1. Hello, Kedra,

      Please try this formula:
      =(COUNTIF(INDIRECT("'Prod - Resolved'!B3:B500");"1 - Critical"))

  25. "For example, you can use the following array formula (remember it requires pressing Ctrl + Alt + Enter)"

    Ctrl+Alt+Enter ???? I don't think so.

    1. Hi Sandy,

      Of course, it's CTRL+SHIFT+ENTER. Don't know how Alt could sneak in. Thanks for catching it!

  26. i have a doubt in ms excel. In a column there are many cells in that column i want to take uncoloured cells from that column trough any equation. would you please help me to do it.

  27. Hello Miss, i am trying to use indirect function for the file that is closed but it dose not work unless file is open can you help. filename Account.xlsx DATA:
    A5 = F:\DATA\INVOICES, A6 = 1385_02/14/2015, A7 = Basicinvoice, A8 = $i$37
    FORMULA: C1 = =INDIRECT("'"&A5&"["&A6&".xlsx]"&A7&"'!"&A8)

    1. Hi Aziz,

      I am afraid this cannot be fixed. When an Indirect formula refers to another Excel file, it must be open, otherwise you will get the #REF! error.

      1. How to do the formuala to use in another workbook indirect function when will drag down , sheet number changed and the data changed.

  28. I have your product and love it. Use Merge Tables but have not had cause to explore other tools. Now I have issue where formulas turn to reference if rows are deleted after Table Merge in tab that is reference. Tried absolute and not success. Tried using Indirect Function which work for deletion of reference ROW issue but it doesn't let me drag formulas down page to populate my data for export. Was trying CHAR AND ROW FUNCTIONS but unable to make ASCII character for AA, which would be 65,65. It seems like I'm missing a simple answer. Any help would be appreciated.

    1. Hello Mike,

      I am sorry it's hard to determine the cause of the problem without seeing your data. If you could post this question on our forum and attach a sample forkbook, our support team will try to help.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)