The INDIRECT function helps you to create dynamic references. In other words, you can replace a part of a reference by a variable.
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.
We are going to use the content of the column A to build the references.
Don't link each cell one by one
In a basic approche, you will select the contain of the cell B8 for each worksheet.
It's the worst that 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.
Build the dynamic reference
First insert your reference in the INDIRECT function
With the INDIRECT function, the references must be written as text so between double-quotes.
And the result is exactly the same (good point) 😎😍🤗
Customize your reference
Now, we need to customize the argument of the INDIRECT function to "read" the cells of the column A. And this is very easy.
- 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 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.