**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 Style**shown 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!