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

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

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

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

  4. Just perfect! Thanks!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. Hello Svetlana,

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

    Best Regards

  20. I was badly looking for that function
    Really thx

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

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