How To Use The Excel-Function:
INDIRECT

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The ‘INDIRECT’ function is a fascinating built-in function in Microsoft Excel. It returns the reference specified by a text string.

This reference could be a cell or range of cells. It can even be a worksheet name.

In this article, we will introduce you to INDIRECT, its syntax, and some examples of how you can use it.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Syntax

The INDIRECT function has one required argument and one optional.

The syntax of the INDIRECT function

=INDIRECT(ref_text, [a1])

The first argument is ref_text and it is the required argument. It is the reference to a cell containing one of the following:

• An A1-style reference
• An R1C1-style reference
• A name defined as a reference
• A reference to a cell in text string form

The ref_text argument must be a valid cell reference or INDIRECT will return the #REF! error. The ref_text argument can refer to another workbook.

But, it must be open or the formula will also return the #REF! error.

The second argument is A1 and it is optional.

This can be one of the two logical values, TRUE or FALSE. Its purpose is to specify what type of cell reference the ref_text argument is. If you omit this argument, the formula assumes a TRUE value.

If TRUE, the function will interpret the ref_text argument as the A1-style reference. If FALSE, it interprets the ref_text argument as the R1C1-style reference.

Example 1: basic concept

Let’s turn our attention to what the INDIRECT function looks like in use.

We have a cell with a value (B3) and we are going to use INDIRECT three different ways.

We will use it to return that value in another cell location.

indirect-formulas

Notice that we have created three different variations of the INDIRECT formula in cells D2, D3, and D4.

Kasper Langmann, Co-founder of Spreadsheeto

Yet they all return the same value: $32,435.

The FORMULA column shows the formula used on each row.

This is a demonstration of the three basic ways you can create an INDIRECT formula. Each of these refer to the contents of the exact same cell reference, B3.

So what happens here?

The INDIRECT function turns the ref_text string into a reference. Thus, the string “B3” in this example becomes the cell reference, B3.

Example 2: not just for cell references

We can also use INDIRECT to return a worksheet reference. The worksheet name needs to be the content of the cell we select as the ref_text.

This has some interesting implications when you need to setup cell references from different worksheets in a workbook.

You can also set it up so it is a dynamic solution by using a dropdown list in the cell reference.

Consider a situation where you have a summary sheet and three other sheets from which you want to pull data.

We will name these sheets “Black,” “Red,” and “Silver” for a product color choice.

We will create a table with row references for each worksheet name. Then we can create INDIRECT formulas that use cell references containing those names.

Here is our table on the summary worksheet.

summary-sheet

And we have three different worksheets named appropriately.

worksheet-tabs

The tables on our three sheets contain data for three individual sales reps.

sales-reps

We could type in a formula referring to the cell reference for the month totals on each page.

cell-ref-formula

But by using INDIRECT we can do this more effectively.

indirect-summary

A couple things make this a great opportunity to leverage the INDIRECT function.

Kasper Langmann, Co-founder of Spreadsheeto

First, if you add more worksheets, your cell reference on that sheet remains unchanged. Then you add another row on the summary table with that sheet’s name.

By copying the existing formulas, you can pull the required data.

This is more efficient that placing direct cell references. That requires navigating to each sheet.

Example 3: INDIRECT as a dynamic reference

We can take our previous example a step further and use INDIRECT to refer to a cell with a drop-down list.

Relating this to our “child” worksheets, we can create a drop-down list for all the color sheet names.

If we set our ‘INDIRECT’ formula to that cell reference, we can pull the monthly totals for each “child” sheet.

We can easily do this by changing the drop-down selection.

dropdown-selection
new-indirect-formula

This is a great example where the INDIRECT function shows how useful it can be.

This is an effective solution.

new-summary

Conclusion

This quick article only scratches the surface of what INDIRECT is capable of. Even so, you can see how powerful even our simple examples are.

Once you begin to master the INDIRECT function, you will be sure to think of other challenges it can solve.

This is one function that can help you tame the largest and most complicated workbook files.

Kasper Langmann, Co-founder of Spreadsheeto