Syntax Explained
INDIRECT (ref_text,[a1])
Above you’ll see the Syntax in Excel language, the part in brackets is where the magic happens. So, let’s break these both down and explain what each element does, but before we do, let’s explain what the term A1 and R1C1 Style mean.
This will affect how you format this formula and applies to other areas in Excel, so it’s important to understand its relevance. A lot of sites won’t explain this without searching for it, so personally, I’ve found it very helpful.
Excel Options Menu under Formulas
From the Excel Options menu under Formulas, you can at any time change the way the columns are labeled from A, B, C, D … etc. (or A1 Style), to numbered columns, so A becomes 1, B becomes 2, and so on (or R1C1 Style). The R1C1 Style is formatted to R for Row first and then C for Column. Compare this format to the A1 Style, which, you guessed it is Column first, then Row (reversed!), and you have a recipe for confusion!
Using the same data set as above but changing the Formulas under Options to R1C1 Style, it looks a little something like this.
The RC [-3] + RC [-2] translates to Row (this row) and Columns (3 to the left) + Row (this row) and Columns (2 to the left). It yields the same result as above, but the address mechanism is relative to its formula. This is useful in VBA and more complex formulas.
Imagine yourself in the middle of the blue arrows, and you want to move one cell in the direction of one of the arrows; this should help you understand in more detail. It’s all relative to where you start.
So, let’s get back over to the Syntax; see below:
Ref_text
Excel describes it as the “reference to a cell that contains an A1 or R1C1 style reference, a name defined as a reference, or a reference to a cell as a text string.”
Each option of the above is explored in more detail below under heading Examples.
[a1]
As per Excel, it defines as “a logical value that specifies the type of reference in Ref_text: R1C1 style = FALSE; A1 style = TRUE or omitted.”
Examples
Ok, let’s make this a little clearer with some examples and pictures; see below:
How to Use Excel INDIRECT Function in A1 Style
In the example above, we have:
- Some data, with a header, in RANGE (B2:B7).
- A cell reference in the A1 Styleshown in cell D3, you know what A1 Style this means if you’ve read the section under Syntax explained above; if not, what are you doing here. Get back up there and read!
- Finally, here’s our first simple INDIRECT formula, with the ref_text element as (D3).We’ve omitted the [a1] element, so the formula assumes this part of being TRUE (or A1 Style). Using INDIRECT (D3) yields the same results as INDIRECT (D3, TRUE)
- In summary, the INDIRECT function has been asked to look at cell D3, and it should expect to find text in the A1 Style of referencing. It then takes the discovery and uses it to return the information found in cell B3; in this case, it’s the word ‘This’.
Changing the reference in cell D3 from B3 to B7 will yield a different result, all making perfect sense, isn’t it?
INDIRECT in R1C1 Style
Change the Formulas in the Options from A1 to R1C1 Style, and the cell address of D3 is now changed as per the formula bar below. We now need to change the omitted (A1 Style) part of the [a1] element to FALSE (R1C1 Style), you’ll see from adding this in, it returns a #REF! Result, that’s because the text in the cell is NOT in R1C1 Style.
Defined Name
If you don’t know about defined names, you’re about to. There are a number of ways to ‘name’ a range; we’ll look at one for now. If you have a range of data you want to reference, or a cell, in this example it’s the latter; simply highlight the cell and click in the address box shown with a blue highlight below.
Overtype this cell address with a name; in this case we’ll call it Ref
Now, we can change the INDIRECT formula to look at the named range; in this case, it’s looking for Ref, and we’ve just told Excel that we’d like cell D3 to be called just that.
Pressing enter will yield the same result again.
Reference to A Cell As A Text String
In order to do this, we need to reference cell B7 as a text string, technically ‘B7’, is text but to elaborate on this and include the sheet name too, it’ll make a whole lot more sense when you realize its potential to make spreadsheet super dynamic. See the last example below for more detail.
If you were to go to another sheet in the same workbook and in any cell, press = and then click on your sheet with data (in this case, the sheet is called Test Data), and click cell B7. Pressing enter would give you a formula like below.
= ‘Test Data’!B7
This is the full-text string address of where we find examples on sheet Test Data. Copy the text from this cell (excluding the = sign) and paste it into the INDIRECT formula over the previous example Ref and it’ll look a little like below. Remember, this is text, so in order to tell the formula it’s a text, we need to enclose it with quotation marks on either side.
I’ve put double spaces between the quotation marks and the copied text; in order for this to work, you need to delete those spaces, like below, and giving us the expected result.
The latter is one of my favorite uses when summarizing any data on one sheet from many, and you want a degree of flexibility without changing the formulas every time.
Here I’ve used INDIRECT combined with CONCATENATE to give the text string address of a cell with the total for January, as found on the sheet titled Jan, and returning the data found on that sheet in cell C9.
I like this because I can now just change the month name on the Summary sheet to Feb or Mar and it’ll update to look at that sheet straight away.
As with any formula, they have their limitations, the right times to use them and the wrong times. Mistakes will be made; things will be learnt, that’s how it goes with Excel, you’ll sit there for a while looking at an R1C1 address only to realize you were calculating Column first THEN Row, I’ve been there!
Risks with this latter example, note that the cell address is fixed to cell C9 only, move that Row out of that address, and the formula will not work!
