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

  1. Hi There!
    I am trying to use the formula =INDIRECT("'"&C3&"'!&F2"), (where in C3 - is the sheet name = Match 3, and F2 is the cell in that sheet containing one of the team's name). However this formula is not returning the desired result (I want team's name that participated in match 3), rather it returns #REF!. Not sure if there is a checkbox that needs be enabled or something here, to disable the #REF! error.

    1. Hi! If I understand your task correctly, the following formula should work for you:

      =INDIRECT("'"&C3&"'!"&"F2")

  2. I am looking to return a status text from a second sheet in specific cell

    My main sheet has in column C row 3 the names of my sheets - this sheet is called "Test Sheet 123"

    I have in the "Test sheet 123" in cell B2 a text "Closed"

    From my main sheet I have in cell G2 tried the following formula

    =INDIRECT(C3!B2) but that does not get the value "Closed"
    =INDIRECT(C3&"!B2") but that does not get the value "Closed"
    =INDIRECT("'" &C3 & "!B2") but that does not get the value "Closed"

    Anyone that can help me ?

  3. Hey

    I am trying to use the indirect function with the 'address' function. The address function worked just fine but when I add the indirect function to it, I keep geting a #ref error. This is the formula I have written '=ADDRESS(income_min_row, COLUMN()) &"-" &ADDRESS(income_max_row, COLUMN())'.

    Anyone with a similar challenge? A solution will be appreciated.

    Thanks

  4. Hi, very good I am happy for finding this site, Tanks for this training articles

  5. =INDEX(INDIRECT("'"&$T11&"'!$A$3:$S500"),MATCH($A11,INDIRECT("'"&$T11&"'!$A$3:$A500"),0),MATCH($W$1,INDIRECT("'"&$T11&"'!$S$3:$S500"),0))
    Formula is entered into cell W11 for reference.

    For the array value, I want to reference another sheet in my workbook based on the salesman's name that is listed in the same row that my formula is entered into (T11), then on that sheet I want to match the listed job number (A11) for the ROW reference, and match the column that contains the same header (W1 - Discount %). Above is the formula that I have, but I am getting an N/A error. I am not sure what is causing the error. On the referenced sheet, if the cell contains a formula for the job number, would my formula still be able to pick up the returned value? IF this is the cause of the error, suggestions on how to fix?

  6. Want to reference a defined data name based on the value in a cell.

    I have defined data names Itemized_Deduction_Amt and Standard_Deduction_Amt. I have in cell C1 a formula the returns either the word Itemized or Standard. I want to use C1 result to construct the data name to be used. For instance,C1 & "_Deduction_Amt" where C1 = Itemized will create data name Itemized_Deduction_Amt. When C1 = Standard, data name would be Standard_Deduction_Amt.

    Instead of using the following formula
    If(Itemized_Deduction_Amt > Standard_Deduction_Amt,Itemized_Deduction_Amt,Standard_Deduction_Amt)
    I want to just use the created data name.
    I tried several ways including the following that did not work:
    = Indirect(C1 & "_Deduction_Amt")
    = C1 & "_Deduction_Amt"

    Thank you

    1. Hello!
      This formula works for me:

      =INDIRECT(C1 & "_Deduction_Amt")

      Maybe there are extra spaces or other extra characters in your values.

  7. =AND(ISNUMBER(SEARCH("-",[@[PO number]])),LEN([@[PO number]])=13)

    =AND(ISNUMBER(SEARCH("-",INDIRECT("[@[PO number]]"))),(LEN(INDIRECT("[@[PO number]]")=13)))

    i am doing something wrong but i have no idea what as the first one works then i turn in to data validation

    1. as far as I can tell the ISNUMBER INDIRECT version always comeback as false and the Len checker doesn't work at all

    2. Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

      1. yes I am check if a number is enter in the cell is a number and has 13 characters including a -

        i.e. 2000000-000000

        all numbers are stored in table PO number, table name Dailyintake

          1. I know have a good understanding of Regex 200000-000000

            =AND(LEN(Table1[@Data])=B11,COUNT(FIND(MID(B6,ROW(INDIRECT("1:"&LEN(B6))),1),numbersAllowed))=LEN(B6),MID(Table1[@Data],7,1)="-",(MID(A2,7,6)*numbersAllowed),LEFT(Table1[@Data],6)*numbersAllowed,MID(Table1[@Data],8,6)*numbersAllowed)

            How would I turn this in to the INDIRECT is the something that i can read that cover this

            1. Hi!
              It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

              1. =AND(LEN(INDIRECT("Table1[@Data]"))=13,COUNT(FIND(MID((INDIRECT("Table1[@Data]")),ROW(INDIRECT("1:"&LEN("-"))),13),numbersAllowed))=LEN(INDIRECT("Table1[@Data]"))="-",(MID(INDIRECT("Table1[@Data]"),7,6)*numbersAllowed),LEFT(INDIRECT("Table1[@Data]"),6)*numbersAllowed,MID(INDIRECT("Table1[@Data]"),8,6)*numbersAllowed)

                I removed all the cell refences

              2. yeah I broke it ill get it eventually

                Table1 is the table name

                Data is the headers on the tables name

                200000-000000 is the cell data

            2. I have got it fantastic

              =AND(LEN(INDIRECT("Table1[@Data]"))=B11,COUNT(FIND(MID(B6,ROW(INDIRECT("1:"&LEN(B6))),1),numbersAllowed))=LEN(B6),MID(INDIRECT("Table1[@Data]"),7,1)="-",(MID(INDIRECT("Table1[@Data]"),7,6)*numbersAllowed),LEFT(INDIRECT("Table1[@Data]"),6)*numbersAllowed,MID(INDIRECT("Table1[@Data]"),8,6)*numbersAllowed)

          2. ill definitely have look at that as it looks like it might do what i want it to do

          3. that's strange because this =AND(ISNUMBER(SEARCH("-",[@[PO number]])),LEN([@[PO number]])=13) seems to work when its in a cell refencing the table it will result in the correct true or false statement when used like that but completely brakes when i turn it in to a INDIRECT version for the use in data validation.

        2. if it fails ether of these checks I want to not let me enter the number

  8. I have values in A column in B1 I have text as SUM. Hence in C1 I have to get sum of values of A Column and if write AVERAGE in B1 than in C1 I have get average values of A column

    1. Hi!
      Excel cannot use your text in a formula as the function name. However, you can create a formula with conditions using the IF function.
      For example,

      =IF(B1="sum",SUM(A1:A100), IF(B1="average",AVERAGE(A1:A100),""))

  9. Hello,

    I am attempting to compile the sum of data from multiple different sheets under certain criteria.

    Example: Summary, Sheet2, Sheet3. On Summary in A1, sum total number amounts from Sheet2 in B:B if Value in C:C = Green, then again on Summary in B1, the sum total number amounts from Sheet3 in B:B if Value in C:C = Green. I hope this makes sense.

    I learned from this page how to pull individual certain cell data but not the sum of data under a specific criteria. It may combine with the Countif or Sumif function but cant figure out how to make it work. I greatly appreciate your help. Thank you.

  10. Hi
    My current issue is i have to sheets lets say
    Sheet1 is where the formula is so
    Countif(sheet2A:A;$C$3) this formula will be in cell C5 and the C6,C7, C8

    What i need is to move the auto fill series to move ranges on sheet2 to be filled as the formula on C6 to query B:B, C7 to query C:C and so on

    Can you help me with the formula syntax please

  11. Hi. Thank you for a great guide. It helped me simplify my spreadsheets a lot.

    I was wondering if there is a way to make the name of the reference sheet change dynamically?

    I have a spreadsheet for each week of the year. The sheet is named after the week, then a comma, then a space and then the year. E.g: "1, 2023" "2, 2023" etc.
    The cells I want to access are always in the previous sheet. In other words: "'this_sheet's_weeknumber-1, 2023". Is there any way to do this so that when I copy the sheets from the week before, I don't have to change any formulas?

    It's not a big deal, since I manually only have to change 1 number every time I make a new sheet, however it would be fun to make it so I don't have to manually change anything.

    1. I like to avoid manual change as you like.
      I believe you could use the function (Sheet) to solve your issue.
      Hope all the best for you

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

  13. =INDIRECT("'" & $A$1 & "'!" & B1)
    this formula not working my workbook.

    my workbook are many sheets but i was collect a data in one sheet..
    many sheets ex. 1 2 3 4 5 6
    & main sheet Sum. =INDIRECT("'" & $A$1 & "'!" & B1)

    1. Hi!
      Look carefully at the example in the article above. Cell B1 should contain the address of the cell.

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

  15. Hello,

    how can i use indirect for different sheet and variable row number?

    thanks

    1. Hi!
      Pay attention to the following paragraph of the article above – Creating a dynamic reference to another sheet.
      You can write the sheet name in a separate cell and refer to it in a formula.

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

  17. This is an excellent article. My problem seems not addressed exactly with the INDIRECT and ROW syntax

    Here is what I am trying to do:

    I have a row of stock values in row 12 in Excel with the headings above that row, row 11. Like so:

    Date Open High Low Close Adj Close* Volume
    Jan 31, 2022 173.88 175.28 172.75 173.40 173.47 65,518,109

    In another column to the right, I have the 12 months of the year, starting with January down to December.

    In the column right next to the months, I have a formula to calculate the Net Value for that month.

    Each close of the month, I insert a new cells below row 11 (the headings) containing the same data as above only with the next last day of the months values. So in the above, it will be February 28th.

    I want January's Net Value to stay the same. But I want February's value to now be of off the new inserted row.

    I cannot seem to get there with the INDIRECT and ROW functions. If I insert a new row, January's Net Value changes to February's.

    Can you help?

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice.
      What formula do you use to calculate? Are you inserting a new blank row 12 and writing February data into it? In this case, the January data is moved to row 13?

      1. It is comprised of two actions at the present time.

        The first is a Macro to move down the date one row.

        Sub APPL()
        '
        ' StockUpdate Macro
        ' Process to update Yahoo's Data in Spreadsheet
        '
        ' Keyboard Shortcut: Ctrl+a
        '
        Range("A32:H32").Select
        Selection.Copy
        Selection.Insert Shift:=xlDown
        End Sub

        The second was /is this formula in Column J.
        =INDIRECT("E"&60-ROW())*500-1000

        When I add new data in "A32:H32" the last day of each month, the formula in Column J calculates the net value. What I desire is Column J's previous month to stay the same, but each new month calculate the new value.

        What is happening now is the new value replaces the existing values all the previous months. I want to keep that historical record so to speak.

          1. Each month, at the end of the month, I obtain from Yahoo the ending Hi, Low , and close of a give stock price.

            I inset that row of data in a row above last months stock information.

            I take the closing price from that row and multiply it by number of shares and then subtract the original purchase price. This gives me the loss or capital gains.

            This gain or loss gets copied to another column where it is place next to a column with the month of the year. The months go down.

            I want that monthly value to stay the same as listed for each past month, but update for each new month.

            Right now my formula as I drag down changes and so does then the previous months values.

            Inputting new rows breaks the use of fixed references with $ signs.

            1. Let change gears and see if this will work with your help. I am so close . . .

              I have this code below to take a variable that changes and place it in another cell, and then as the variable changes each month (like what I am trying to do above), keep the last variable, while updating the next row with the new variable.

              It works, except the VBA code works only IF the data is inputted by hand. IF the data is changed by reference to another cell or workbook i.e. A1= sheet6 C1 and C1 changes the A1 value then it does - unfortunately!!! - not work.

              Is there a way to fix this problem?

              Private Sub Worksheet_Change(ByVal Target As Range)
              If Target.Address = "$B$2" Then
              a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
              Sheets("Sheet2").Range("A" & a).Value = Sheets("Sheet1").Range("B2").Value
              End If
              End Sub

  18. Many times i feel very confused with how to handle it in excel. I really need articles like yours. I felt very inconvenient, so I went to apkdownload and found something to help. Crazy, there are a few apps that can share how to or tips for excel. But I still prefer articles like yours

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  38. 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()),")")))

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

  40. Best wishes; from everyone of us.

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

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

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

    THANK YOU

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

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

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

  47. Very very helpful, thank you!

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

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

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

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