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 4. Total comments: 215

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

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

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

    THANK YOU

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

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

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

  7. Very very helpful, thank you!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  26. 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"?

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

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

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

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

  31. after applying indirect function on data if some data is added is this function applied to that data..i checed that but it is not taking the newly added data ..
    thank u

  32. Ablebits.com i love you too much in-fact you've made me like and understand excel so much thank u.

  33. Excellent article...

    Dik

  34. I have two sheets Sheet1 and Milk, I intend to get data from sheet "Milk" sheet1. I "=Milk!$AJ30" put this in formula bar, i need to get fixed column but varied row in Sheet1. I could not get the desired result.
    I only get fixed column AJ and same Row 30 every time when I drag by the handle. it works vertically not horizontally.
    i want result in the rows.
    Regards
    Qamar

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

    Robert this error may be because you are NOT using sheet reference.

    Let says your sheet name is "XYZ". IN any sheet put A1- XYZ

    Then use indirect like INDIRECT(A1&"!"&"a2:b3")....
    Here A1 will refer to XYZ
    ! refer to XYZ!
    A2: B3 : if lets say is your sheet range.

    This will give you sheet reference

  36. Wow thank you.. You re genius.. Really help

  37. sheet name is x,
    I have a column name Ex: 7(g:g),

    I want to use Indirect with sheetname (X) and column number 7(g:g), column number will be changed 8 and 9 and 10.... any idea...

  38. Hello,
    my question is the following :

    I wish to use the cells in column E to reference the value of its correlative C cell WHEN the value in the E cell is 1.

    In the F1 cell, is where I would like for this value to appear.

    I have tried with the INDIRECT function, and I think it's the most probable answer, but I can't seem to get it quite right.
    e.g.

    In F1 must appear value in C2 if E2=1 (if E2 is blank or 0, omit)
    In F1 must appear value in C3 if E3=1 (if E3 is blank or 0, omit)
    In F1 must appear value in C4 if E4=1 (if E4 is blank or 0, omit)
    and so on...

    Is there any simple formula to do this?

    Thanks, in advance.

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

  40. Using indirect address with match sum up the salary for respective months.start month is Feb and end month is june
    A salary
    Jan4000
    Feb2000 stare end totsal
    Mar1000 feb june
    Apr5000
    May3000
    June 1ooo

  41. Buenas noches

    estoy tratando de aplicar la formula =INDIRECT(SUBSTITUTE,(B2,"",")) pero al momento de darle aplicar el sistema no me la reconoce me podrian colaborar por favor gracias

  42. There are no quirk in Excel with indirect function.
    You misunderstood the indirect(R3C) reference. As the function is written in Cell D5 (row3, colonne4) it directly go and catch the value in (Row3, colonne4)

    indirect(R3C4) = is looking for Row3 Colonne4
    When no reference to the number of the column is done (indirect(R3C) then it refers to the column where the formula (look for which cell the equal(=) sign is)

    Also you should change your blue array in you sreenshot. The array from C5 to C3 should be change into C5 to D3

    As a conclusion the formula in D5: "=Indirect(C5,false)" return the value of R3C3 as expected (ie "R2C1")

    If you want the indirect to go further and catch 222 (Cell A2) then you should have written:
    =INDIRECT(INDIRECT(C5,false),false)

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

  44. Just perfect! Thanks!

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

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

  47. Employee sheet details
    Employee ID Group Name Machine Name Uniq ID Start Date End Date NumberOfAssets Machine Added to Domain
    8002 IT Navaneeth.Sarvamanya nsarvamanya 8002-nsarvamanya - - 2 Y
    8016 Engineer Suraj Bhasin sbhasin 8016-sbhasin - - 3 Y

    Inventory sheet details
    Type Model # Serial # Uniq ID Procurment Date End Date Hardware Condition Status
    Laptop Official Z930 1E137603H Z930-1E137603H - - Available
    Laptop Official Z930 3E099576H Z930-3E099576H 01-06-2014 31-05-2017 Lost

    AssetLog sheet details

    Start Date End Date Asset Employee Name Remarks Hardware Condition
    01-02-2013 - Z930-1D094026H 8001-achhabra Working

    Issue in the formulae
    ActiveCell.Value = "=INDEX(Inventory!$E$6:$E$264,MATCH(""Management""&""Z930*""&""Available"",INDIRECT(""Employee!""&(""c""&ro))&Inventory!$E$6:$E$264&Inventory!$I$6:$I$264,0))"

    ro is the variable which is row number which is specified; ro is not getting resolved hence I am getting #Name can you suggest me how to solve this issue?

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

  49. Hi all,

    I need your support. I have 3 drop down list where I have used indirect functions where box B depends on box A & box C depends on Box B and this have to work from A to C instead. I was able to lock A if is there any information on B ( I mean, box A not allow to filter only when box B is empty)
    My concern is that I'm not able to lock box B for selection if the box C has information.
    I would appreciate your support.

    Regards

  50. Hi I am creating a report spread sheet with a YES or NO box. If the box has YES in it I want he workbook to open another sheet or if NO then stay on the same sheet, is this possible.
    Cheers Steve

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