**What does it do?**

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

**Excel Options** **menu under** **Formulas**

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!

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

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.

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:

**How To Use Excel INDIRECT Function ****in A1 Style**

**in A1 Style**

In the example above, we have:

1. Some data, with a header, in **RANGE (B2:B7).**

2. A cell reference in the **A1 Style** shown in **cell D3**, you know what **A1 Style** this means if you’ve read the section under **Sytnax explained** above, if not, what are you doing here! Get back up there and read!

3. 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 to be **TRUE (or A1 Style). **Using **INDIRECT(D3)** yields the same results as **INDIRECT(D3,TRUE)**

** **** 4.**** I****n 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 informatio****n 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**

How To Use Excel INDIRECT Function? 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?

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

**How To Use Excel INDIRECT Function ****Defined Name**

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

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.

**How To Use Excel INDIRECT Function ****Reference to a cell as a text string**

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

How To Use Excel INDIRECT Function ? 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.

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

How To Use Excel INDIRECT Function ? 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!