Functions that make your file DYNAMIC: Part One: The INDIRECT function

This blog is divided into two parts. First, we discuss the indirect function. Not many people use this formula. The benefits of using this are huge!
The function of this INDIRECT is to facilitate the search of values within a spreadsheet using text references.

1 Syntax

The breakdown of this is:

• Ref_text indicate to a text string that contains the reference to a cell or a named range. This must be a valid cell reference, or else the function would return a #REF! error.
• [a1] (optional) is a logical value that specifies what type of reference to use for ref text.

This could either be

o TRUE (indicating A1 style reference) or
o FALSE (indicating R1C1-style reference).
If omitted, it is TRUE by default.

2 How does this formula works?

To get the base idea of the indirect function, let us write a simple formula.
Suppose you have in cell A1 50 and in cell B1 text A1.

Figure 1: Base example – a

In cell C1 we put the INDIRECT function which refer to the cell B1 which is A1.
The function will lead to cell A1 where it picks the value to return, which is 50 (see figure 2).

Figure 2: Base example – b

3 Example

Next, I will discuss a more practical example. Below I have some data specifications about cars (see figure 3).

Figure 3: Car specifications

Suppose you want that column G (from cell G5 till cell G14) is referring to the width of the car (from car 1 till car 10).
You could use a static formula, namely cell G5=B5 (ceteris paribus = B6 .. B14)

Figure 4: Car specifications – Static formula

But maybe the end-user wants to know the height of a specific car. So instead of column B, he is interested in column C. That means that I must change all individual formulas because this is a static function. I could use the INDIRECT function to solve this issue.

How can I do this?

Figure 5: Car specifications – Dynamic formula – result column C

Solution: In cell G5 till G14, we want to refer indirect to the cell C5 to C14.

• C = the column which the end-user wants to determine. We use cell G1 to refer it.
• The rows where the specific cell is located, we will refer with the ROW function.Combining this gets the formula INDIRECT(G$1&ROW())

Now If I change G$1 to column D, I will get the results in next figure:

Figure 6: Car specifications – Dynamic formula – result column D

It allows for much more versatility. This is very useful.

I hope the INDIRECT formula helps you a lot from now on!