### Leave a Comment:

###### 1 comment

[…] post The Excel INDIRECT function appeared first on Earn and […]

Reply- You are here:
- Home »
- Blog »
- Excel
- » The Excel INDIRECT function

In brief summary, the Excel INDIRECT function returns a reference specified by a text string.

**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 the way you format this formula and also applies to other areas in Excel so it’s important to understand it’s relevance. A lot of sites won’t explain this without searching for it, so personally I’ve found it very helpful.

From the Excel Options menu under Formulas, you can at any time change the way the columns are labelled 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!

In summary, A1 Style is the address co-ordinates of a cell, see below example where the SUM of C3 + D3 will result in an answer of 220.

**Click to enlarge**

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 where the formula is. This is useful in VBA and more complex formulas.

**Click to enlarge**

Imagine yourself in the middle of the blue arrows and you want to move 1 cell in direction of one of the arrows, this should help you understand in more detail. It’s all relative to where you start.

So, with that out the way, let’s get back over to the syntax, see below:

**Ref_text******

As per 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 this 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:**INDIRECT in A1 Style**

**Click to enlarge**

In the example above, we have:

**Click to enlarge**

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 cell is NOT in R1C1 style.

Based on what you’ve seen so far, can you figure out what the R1C1 address would look like for cell B7? Remember? Clues are in the second paragraph under Syntax, what would the address be in numerical format?

**Click to enlarge**

Got it? I knew you would, its R7C2. See below for the correction. It’s Row 7 and Column 2.

**Click to enlarge**

**Defined Name**

If you don’t know about defined names, you’re about to. There’s 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.

**Click to enlarge**

Overtype this cell address with a name, in this case we’ll call it Ref

**Click to enlarge**

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.

**Click to enlarge**

Pressing enter will yield the same result again.

**Click to enlarge**

**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 realise it’s potential to make spreadsheet super dynamic. See last example below for more detail.

If you were to go to another sheet in the same workbook and in any cell, press = and the 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 example 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 text, we need to enclose it with quotation marks either side.

**Click to enlarge**

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.

**Click to enlarge**

The latter is one of my favourite uses when trying to summarise any data on one sheet from many and you want a degree of flexibility without having to change the formulas every time. See below.

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.

**Click to enlarge**

**Click to enlarge**

What I like about this is 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, right times to use them and 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 realise 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!

Now before you go, grab hold of my 27 formulas book. While INDIRECT isn't one of them, I hone in on the 27 formulas that will really simplify your work. Sign up below and get a free copy in your inbox.

The Excel INDIRECT function – Earn and Excel says September 22, 2017

[…] post The Excel INDIRECT function appeared first on Earn and […]

Reply