The INDIRECT function helps you to create a dynamic references of your cells. In other words, you can replace a part of a reference by a variable. Let's see that through 2 examples
Presentation of the function INDIRECT
Let's suppose you have a workbook with a lot of worksheets. Each worksheet has the same structure with these informations.
You want to create a worksheet that return the calling code for each country.
As you can notice, the value in the column A is the same that the worksheet name. This will be our golden thread.
In a basic approche, you will select the contain of the cell B8 for each worksheet.
It's the worst you can do 😱😱😱
Let's focus on the references
As you can noticed, the writing of a reference is
So the idea is to replace the worksheets' reference (FR, BE, IT, ...) by the values of the column A.
In this situation, the INDIRECT function is compulsory.
The same reference with INDIRECT
If you write the reference of a cell in an INDIRECT function, you must write the reference between double quotes.
Have you seen the result in the cell is exactly the same with the function INDIRECT 😎😍🤗
Customize your reference
Now, we need to customize the parameter of the INDIRECT function to "read" the cells of the column A. A. And this is very easy.
- You keep between double quotes the part of the reference that doesn't change
- Out of the double quotes, you select the value that you want to include in your reference
- You link both part with the symbol &
In our example, we will write our function like this
Let's analyze the function
- A2 is the value of the cell A2 ; FR
- We use the & to link this reference with the invariant part
- The invariant part (the one that is always the same) is writing between double quotes
Be careful with the spaces
If the name of your worksheet has blank (or space), the worksheet's name will be write between simple quotes.
Here we have 'United States' for the sheet's name
WE MUST KEEP THE SIMPLE QUOTES !!!
In that case, you must keep the symbol "simple quote" inside your INDIRECT function.
So we write the function like this
And it works 😊😎😍
To clarify, the writing, the first part of the function is double-quote/simple-quote/double-quote.
Example with the INDIRECT function
In the following video, you will see and example with the INDIRECT function to extract randomly data from a list.