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.

A great lot of functions exist in Microsoft Excel, some being easy-to-understand, other requiring a long learning curve, and the former being used more often than the latter. And yet, Excel INDIRECT is one of the kind. This Excel function does not perform any calculations, nor does it evaluate any conditions or logical tests.

Well then, what is the INDIRECT function in Excel and what do I use it for? This is a very good question and hopefully you will get a comprehensive answer in a few minutes when you've finished reading this tutorial.

Excel INDIRECT function - syntax and basic uses

As its name suggests, Excel INDIRECT is used to indirectly reference cells, ranges, other sheets or workbooks. In other words, the INDIRECT function lets you create a dynamic cell or range reference instead of hard-coding them. As a result, you can change a reference within a formula without changing the formula itself. Moreover, these indirect references won't change when some new rows or columns are inserted in the worksheet or when you delete any existing ones.

All this may be easier to understand from an example. However, to be able to write a formula, even the simplest one, you need to know the function's arguments, right? So, let's have a quick look at Excel INDIRECT syntax first.

INDIRECT function syntax

The INDIRECT function in Excel returns a cell reference from a text string. It has two arguments, the first is required and the second is optional:

INDIRECT(ref_text, [a1])

ref_text - is a cell reference, or a reference to a cell in the form of a text string, or a named range.

a1 - is a logical value that specifies what type of reference is contained in the ref_text argument:

  • If TRUE or omitted, ref_text is interpreted as an A1-style cell reference.
  • If FALSE, ref_text is treated as a R1C1 reference.

While the R1C1 reference type might be useful in certain situations, you'll probably want to use the familiar A1 references most of the time. Anyway, nearly all INDIRECT formulas in this tutorial will use A1 references, so we will be omitting the second argument.

Basic use of the INDIRECT function

To get into the function's insight, let's write a simple formula that demonstrates how you use INDIRECT in Excel.

Suppose, you have number 3 in cell A1, and text A1 in cell C1. Now, put the formula =INDIRECT(C1) in any other cell and see what happens:

  • The INDIRECT function refers to the value in cell C1, which is A1.
  • The function is routed to cell A1 where it picks the value to return, which is number 3.
Using the INDIRECT function in Excel

So, what the INDIRECT function actually does in this example is converting a text string into a cell reference.

If you think this still has very little practical sense, please bear with me and I will show you some more formulas that reveal the real power of the Excel INDIRECT function.

How to use INDIRECT in Excel - formula examples

As demonstrated in the above example, you can use the Excel INDIRECT function to put the address of one cell into another as a usual text string, and get the value of the 1st cell by referencing the 2nd. However, that trivial example is no more than a hint at the INDIRECT capabilities.

When working with real data, the INDIRECT function can turn any text string into a reference including very complex strings that you build using the values of other cells and results returned by other Excel formulas. But let's not put the cart before the horse, and run through several Excel Indirect formulas, one at a time.

Creating indirect references from cell values

As you remember, the Excel INDIRECT function allows for A1 and R1C1 reference styles. Usually, you cannot use both styles in a single sheet at a time, you can only switch between the two reference types via File > Options > Formulas > R1C1 check box. This is the reason why Excel users rarely consider using R1C1 as an alternative referencing approach.

In an INDIRECT formula, you can use either reference type on the same sheet if you want to. Before we move any further, you may want to know the difference between A1 and R1C1 reference styles.

A1 style is the usual reference type in Excel that refers to a column followed by a row number. For example, B2 refers to the cell at the intersection of column B and row 2.

R1C1 style is the opposite reference type - rows followed by columns, which does take some time getting used to : ) For example, R4C1 refers to cell A4 which is in row 4, column 1 in a sheet. If no number comes after the letter, then you are referring to the same row or column.

And now, let's see how the INDIRECT function handles A1 and R1C1 references: INDIRECT formulas with A1 and R1C1 references

As you see in the screenshot above, three different Indirect formulas return the same result. Have you already figured out why? I bet you have : )

  • Formula in cell D1: =INDIRECT(C1)

This is the easiest one. The formula refers to cell C1, fetches its value - text string A2, converts it into a cell reference, heads over to cell A2 and returns its value, which is 222.

  • Formula in cell D3: =INDIRECT(C3,FALSE)

FALSE in the 2nd argument indicates that the referred value (C3) should be treated like a R1C1 cell reference, i.e. a row number followed by a column number. Therefore, our INDIRECT formula interprets the value in cell C3 (R2C1) as a reference to the cell at the conjunction of row 2 and column 1, which is cell A2.

Creating indirect references from cell values and text

Similarly to how we created references from cell values, you can combine a text string and a cell reference within your INDIRECT formula, tied together with the concatenation operator (&).

In the following example, the formula: =INDIRECT("B"&C2) returns a value from cell B2 based on the following logical chain:

The INDIRECT function concatenates the elements in the ref_text argument - text B and the value in cell C2 -> the value in cell C2 is number 2, which makes a reference to cell B2 -> the formula goes to cell B2 and returns its value, which is number 10. Making indirect references from cell values and text

Using the INDIRECT function with named ranges

Apart from making references from cell and text values, you can get the Excel INDIRECT function to refer to named ranges.

Suppose, you have the following named ranges in your sheet:

  • Apples - B2:B6
  • Bananas - C2:C6
  • Lemons - D2:D6

To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1).

And now, you can take a step further and imbed this INDIRECT formula into other Excel functions to calculate the sum and average of the values in a given named range, or find the maximum / minimum value within the rage:

  • =SUM(INDIRECT(G1))
  • =AVERAGE(INDIRECT(G1))
  • =MAX(INDIRECT(G1))
  • =MIN(INDIRECT(G1))
Using the INDIRECT function with named ranges

Now that you've got the general idea of how to use the INDIRECT function in Excel, we can experiment with more powerful formulas.

INDIRECT formula to dynamically refer to another worksheet

The usefulness of the Excel INDIRECT function is not limited to building "dynamic" cell references. You can also utilize it to refer to cells in other worksheets "on the fly", and here's how.

Suppose, you have some important data in Sheet 1, and you want to pull that data in Sheet 2. The following screenshot demonstrates how an Excel Indirect formula can handle this task: INDIRECT formula to dynamically refer to another worksheet

Let's break apart the formula you see in the screenshot and understand.

As you know, the usual way to reference another sheet in Excel is writing the sheet's name followed by the exclamation mark and a cell / range reference, like SheetName!Range. Since a sheet name often contains a space(s), you'd better enclose it (the name, not a space : ) in single quotes to prevent an error, for example 'My Sheet!'$A$1.

And now, all you have to do is to enter the sheet name in one cell, the cell address in another, concatenate them in a text string, and feed that string to the INDIRECT function. Remember that in a text string, you have to enclose each element other than a cell address or number in double quotes and link all elements together using the concatenation operator (&).

Given the above, we get the following pattern:

INDIRECT("'" & Sheet's name & "'!" & Cell to pull data from)

Going back to our example, you put the sheet's name in cell A1, and type the cell addresses in column B, as demonstrated in the screenshot above. As the result, you get the following formula:

INDIRECT("'" & $A$1 & "'!" & B1)

Also, please pay attention that if you are copying the formula into multiple cells, you have to lock the reference to the sheet's name using the absolute cell references like $A$1.

Notes

  • If either of the cells that contain the 2nd sheet's name and cell address (A1 and B1 in the above formula) is empty, your Indirect formula will return an error. To prevent this, you can wrap the INDIRECT function in the IF function:

    IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))

  • For the INDIRECT formula that refers to another sheet to work correctly, the referred sheet should be open, otherwise the formula will return a #REF error. To avoid the error, you can use the IFERROR function, which will display an empty string, whatever error occurs:

    IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")

Creating an Excel dynamic reference to another workbook

The Indirect formula that refers to a different Excel workbook is based on the same approach as a reference to another spreadsheet. You just have to specify the workbook's name is addition to the sheet name and cell address.

To make things easier, let's start with making a reference to another book in the usual way (apostrophes are added in case your book and/or sheet names contain spaces):
'[Book_name.xlsx]Sheet_name'!Range

Assuming that the book name is in cell A2, the sheet name is in B2, and the cell address is in C2, we get the following formula:

=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)

Since you don't want the cells containing the book's and sheet's names to change when copying the formula to other cells, you lock them by using absolute cell references, $A$2 and $B$2, respectively. Excel INDIRECT formula to refer to another workbook

And now, you can easily write your own dynamic reference to another Excel workbook by using the following pattern:

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

Note. The workbook your formula refers to should always be open, otherwise the INDIRECT function will throw a #REF error. As usual, the IFERROR function can help you avoid it:

=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")

Using Excel INDIRECT function to lock a cell reference

Normally, Microsoft Excel changes cell references when you insert new or delete existing rows or columns in a sheet. To prevent this from happening, you can use the INDIRECT function to work with cell references that should remain intact in any case.

To illustrate the difference, please do the following:

  1. Enter any value in any cell, say, number 20 in cell A1.
  2. Refer to A1 from two other cells in different ways: =A1 and =INDIRECT("A1")
  3. Insert a new row above row 1.

See what happens? The cell with the equal to logical operator still returns 20, because its formula has been automatically changed to =A2. The cell with the INDIRECT formula now returns 0, because the formula was not changed when a new row was inserted and it still refers to cell A1, which is currently empty: Using Excel INDIRECT function to lock a cell reference

After this demonstration, you may be under the impression that the INDIRECT function is more a nuisance than help. Okay, let's try it another way.

Suppose, you want to sum the values in cells A2:A5, and you can easily do this using the SUM function:

=SUM(A2:A5)

However, you want the formula to remain unchanged, no matter how many rows are deleted or inserted. The most obvious solution - the use of absolute references - won't help. To make sure, enter the formula =SUM($A$2:$A$5) in some cell, insert a new row, say at row 3, and… find the formula converted to =SUM($A$2:$A$6).

Of course, such courtesy of Microsoft Excel will work fine in most cases. Nevertheless, there may be scenarios when you don't want the formula to get changed automatically. The solution is to use the INDIRECT function, like this:

=SUM(INDIRECT("A2:A5"))

Since Excel perceives "A1:A5" as a mere text string rather than a range reference, it won't make any changes when you insert or delete a row(s).

Using INDIRECT with other Excel functions

Apart from SUM, INDIRECT is frequently used with other Excel functions such as ROW, COLUMN, ADDRESS, VLOOKUP, SUMIF, to name a few.

Example 1. INDIRECT and ROW functions

Quite often, the ROW function is used in Excel to return an array of values. For example, you can use the following array formula (remember it requires pressing Ctrl + Shift + Enter) to return the average of the 3 smallest numbers in the range A1:A10:

=AVERAGE(SMALL(A1:A10,ROW(1:3)))

However, if you insert a new row in your worksheet, anywhere between rows 1 and 3, the range in the ROW function will get changed to ROW(1:4) and the formula will return the average of the 4 smallest numbers instead of 3.

To prevent this from happening, nest INDIRECT in the ROW function and your array formula will always remain correct, no matter how many rows are inserted or deleted:

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3")))) Using INDIRECT and ROW functions

Here's a couple more examples of using INDIRECT and ROW in conjunction with the LARGE function: How to sum N largest numbers in a range.

Example 2. INDIRECT and ADDRESS functions

You can use Excel INDIRECT together with the ADDRESS function to get a value in a certain cell on the fly.

As you may remember, the ADDRESS function is used in Excel to get a cell address by the row and column numbers. For example, the formula =ADDRESS(1,3) returns the string $C$1 since C1 is the cell at the intersection of the 1st row and 3rd column.

To create an indirect cell reference, you simply embed the ADDRESS function into an INDIRECT formula like this:

=INDIRECT(ADDRESS(1,3))

Of course, this trivial formula only demonstrates the technique. And here are a few examples that may prove really useful:

Using INDIRECT with Data Validation in Excel

You can use the Excel INDIRECT function with Data Validation to create cascading drop down lists that display different choices depending on what value the user selected in the first dropdown.

A simple dependent drop-down list is really easy to make. All it takes is a few named ranges to store the dropdown's items and a simple =INDIRECT(A2) formula where A2 is the cell displaying your first drop-down list. Using INDIRECT with Data Validation in Excel

To make more complex 3-level menus or drop-downs with multi-word entries, you will need a bit more complex INDIRECT formula with a nested SUBSTITUTE function.

For the detailed step-by-step guidance on how to use INDIRECT with Excel Data Validation, please check out this tutorial: How to make a dependent drop down list in Excel.

Excel INDIRECT function - possible errors and issues

As demonstrated in the above examples, the INDIRECT function is quite helpful when dealing with cell and range references. However, not all Excel users eagerly embrace it mostly because extensive use of INDIRECT in Excel formulas results in lack of transparency. The INDIRECT function is difficult to review since the cell it refers to is not the ultimate location of the value used in the formula, which is pretty confusing indeed, especially when working with large complex formulas.

In addition to the above said, like any other Excel function, INDIRECT may throw an error if you misuse the function's arguments. Here's a list of most typical mistakes:

Excel INDIRECT #REF! error

Most often, the INDIRECT function returns a #REF! error in three cases:

  1. ref_text is not a valid cell reference. If the ref_text parameter in your Indirect formula is not a valid cell reference, the formula will result in the #REF! error value. To avoid possible issues, please check the INDIRECT function's arguments.
  2. Range limit is exceeded. If the ref_text argument of your Indirect formula refers to a range of cells beyond the row limit of 1,048,576 or the column limit of 16,384, you will also get the #REF error in Excel 2007, 2010 and Excel 2013. Earlier Excel versions ignore the exceeded limit and do return some value, though often not the one you'd expect.
  3. The referred sheet or workbook is closed. If your Indirect formula refers to another Excel workbook or worksheet, that other workbook / spreadsheet must be open, otherwise INDIRECT returns the #REF! error.

Excel INDIRECT #NAME? error

This is the most obvious case, implying that there is some error in the function's name, which leads us to the next point : )

Using the INDIRECT function in non-English locales

You may be curious to know that the English name of the INDIRECT function has been translated into 14 languages, including:

  • Danish - INDIREKTE
  • Finnish - EPÄSUORA
  • German - INDIREKT
  • Hungarian - INDIREKT
  • Italian - INDIRETTO
  • Norwegian - INDIREKTE
  • Polish - ADR.POŚR
  • Spanish - INDIRECTO
  • Swedish - INDIREKT
  • Turkish - DOLAYLI

If you are interested to get the full list, please check out this page.

A common problem with non-English localizations is not the INDIRECT function's name, but rather different Regional Settings for the List Separator. In the standard Windows configuration for North America and some other countries, the default List Separator is a comma. While in European countries, the comma is reserved as the Decimal Symbol and the List Separator is set to semicolon.

As a result, when copying a formula between two different Excel locales, you may get the error message "We found a problem with this formula…" because the List separator used in the formula is different from what is set on your machine. If you run into this error when copying some INDIRECT formula from this tutorial into your Excel, simply replace all commas (,) with semicolons (;) to get it fixed.

To check which List Separator and Decimal Symbol are set on your machine, open the Control Panel, and go to Region and Language > Additional Settings.

Hopefully, this tutorial has shed some light on using INDIRECT in Excel. Now that you know its strengths and limitations, it's time to give it a shot and see how the INDIRECT function can simplify your Excel tasks. Thank you for reading!

Table of contents

215 comments

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

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

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

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

    • Hello!
      This formula works for me:

      =INDIRECT(C1 & "_Deduction_Amt")

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

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

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

      • 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

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

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

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

          • 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

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

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

              • 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

              • =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

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

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

    • 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),""))

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

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

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

    • 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

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

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

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

  13. Hello,

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

    thanks

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

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

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

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

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

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

            • 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

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

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

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

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

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

    • Hi Shau Leong,

      From all appearances, you are right. Thank you for explaining this case, I will update the example.

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

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