Excel’s odd but useful – Indirect Function

The Indirect function in Excel is an odd but useful function.  On the Microsoft website the description given is

“Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.”

Source:  Office Support

This doesn’t really mean too much to most, but let me explain a little further:

Indirect Function Example

Let’s say you have the number 25 in cell B2 and in cell B4 you have the text B2.  In any other cell if we enter the formula =Indirect(B4), we will get 25.

So what has happened is that the Indirect function has taken the text in cell B4 and converted that to a cell reference, B2.  B2 is equal to 25, so the formula returns 25.

Indirect function in excel

Okay so I realize this is not very exciting, but stick with me because the Indirect function has many useful applications.

But before we look at more examples lets first look at the formula syntax

INDIRECT(ref_text, [a1])

ref_text is required and must contain a reference to a cell or a reference to a cell as a string text

[a1] is optional.  Leave this blank if you are using A1(standard) style referencing.  If you are using R1C1 style referencing then select ‘False’.

The first application I want to show you is how you can use the Indirect function to reference a cell or cells in a different worksheet.  The second application is using Indirect in Excels Data Validation.  Here you will learn how you can create a second drop down which returns values based on the selection made by the first drop down.

 

In return for these tips, if you find either of them useful I ask that you share this post or the video with your friends and colleagues

Sign up for my newsletter – Don’t worry, I wont spam – Just useful Excel and Power BI tips and tricks to your inbox SIGN UP NOW

%d bloggers like this: