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

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

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

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

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

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

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

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

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

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

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

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

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

  13. Excellent article...

    Dik

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

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

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

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

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

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

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

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

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

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

  24. Just perfect! Thanks!

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

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

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

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

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

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

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

  32. i want to indirect to another sheet but show the rows that have specific criteria

  33. I am working on a calculation sheet where i have three different set of inputs and values for the three types of sub categories for example: I have a fruits business where i sell apple, orange, banana. All the three fruits have a different sales and purchase prices and so on. I have created a drop down list for the tree fruits and selected a fruit. As i have selected one fruit i wanted to display the respective calculations in the below cells. If I again change the input the values in the drop down the displayed values below should change accordingly. Can this be possible?

    Thank you in advance.

  34. hi,
    how can i get the value of specific cell value of workbook that when it closed with indirect(without VBA)?

  35. Thank you for the above information. It looks like I am doing this wrong by not using named ranges but I would like to ask anyways.

    I'm working on a project that requires a separate "data" workbook and a number of "template" workbooks that are applied to the data workbook to create new user workbooks on the fly. My question is about using indirect to point to tables.

    In the template workbooks I have a number of formulas that are pointing to the data workbook. When I create a merged user workbook by adding the template workbook to the data workbook, I'm am using VBA to edit out the file pointers to the data workbook so that the new workbook will not be reaching out to the original data workbook, becoming self contained.

    Specifically the template workbook has the data validation list that uses Indirect to successfully to point to the data workbook:
    =INDIRECT("Data.xltm!tblContacts[Name]")

    After the worksheet is copied to the data workbook the edited Formula1 value is:
    =INDIRECT("tblContacts[ContactName]")

    However I'm getting a 1004 error on this code when I try to update the cell:
    Cell.Activate
    With Cell.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=CellAfter
    End With

    Any ideas?

    Thank you

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

  37. I have a drop down box at the top of "summary" worksheet that has the names of all the other worksheets in the workbook. In a later cell, I am trying to use INDIRECT to reference a cell on the sheet that comes from the drop down box, but I can't figure it out. (all the other sheets are formatted the same, so the data I'm looking for is always in the same cell) This way when I select what worksheet I want (from the drop down box), the data automatically fills in down the summary sheet.
    Latest attempt: =INDIRECT("'"&A1&"'!"D10)

  38. I am trying to use indirect to execute a DDE command based on variable. the original command is ='MT4'|HIGH!EURUSD

    I want to create that command in a a cell then execute it in another cell.

  39. Hello Svetlana,

    many thanks for refering to my Excel-Translator.de in your post :-)

    Best Regards

  40. I was badly looking for that function
    Really thx

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

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



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