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.”
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 contains the value 25, so the INDIRECT function returns 25.
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. First, we will look at sheet names that contain no spaces. Then we will look at a more complex example where the sheet name contains a space.
The second application is using Indirect in Excels Data Validation to create a dependable dropdown. Here you will learn how you can create a second drop-down that returns values based on the selection made by the first dropdown.
