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 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.
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
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 won't spam - Just useful Excel and Power BI tips and tricks to your inbox SIGN UP NOW
Sign up for my newsletter - Don't worry, I won't spam - Just useful Excel and Power BI tips and tricks to your inbox SIGN UP NOW
Take A FREE course with us Today!
The Ultimate Excel Formulas Course
-
includes XLOOKUP and will soon include Dynamic Arrays
Become a Power Pivot Hero
GET and TRANSFORM DATA like a PRO
Power Query Excel 365
Do you use the INDIRECT function in Excel? If so, for what purpose? Leave a comment below and earn some cryptocurrency tokens. We are the only Excel blog in the world where you can earn while you learn.
Cross posted from my blog with SteemPress : https://theexcelclub.com/excels-indirect-function/
