With the INDIRECT function, you can customize the reference of your cells and then create dynamic references.
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.
We want to return the capital name for each country in another worksheet.
So, we create a worksheet to return all the capital name and what is interesting to notice, the values in the column A is the same that the worksheets' name (very important to understand the following 😎 )
Don't link each cell one by one
In a basic approche, you will connect the contain of the cell B8 for each worksheet. It's the worst that you can do 😱😱😱
Let's focus on the references
Of course, when you look at all the references, it's easy to notice that the name of the worksheets is the same as the value in column A. So the idea here is : "Instead of creating a lot of manual references, let's create 1 formula with the values of the column A"
The detail of the reference is
- First position the Sheet Name
- Second information the symbol exclamation mark to split the sheet name and the reference of the cell
- Third, we have the reference of the cell.
Build the dynamic reference
So the idea is to replace the worksheets' reference (UK, Italy, France, Spain, ...) by the values of the cells in column A.
Step 1: 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😉. Believe it or not, this first step is very important because you can control that the INDIRECT function 'understands' your reference.
Step 2: 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 (here !B2 with the exclamation mark)
- Out of the double quotes, you select the value that you want to include in your reference (the reference of the cell A2)
- You link both part with the symbol &
In this example, we will write our function like this
Step 3: Copy-Paste your formula
So, the function returns what we expect. We just have to copy-paste this formula to the rest of our document and the job is finish 😀😍😍
Be careful with the spaces
If some of your worksheet name has spaces in its name, the previous formula will return an error #REF (not able to understand the reference).
When you have such error, don't try to correct the formula but return to the basic; I mean just create a connection with the worksheet to understand the writing.
If the name of your worksheet has blank (or space), the worksheet's name will be write between simple quotes 😮😮😮 AND WE MUST KEEP THE QUOTES in the INDIRECT formula!!!
So, the writing is the following
- We must start by the quote between 2 double-quotes "'"
- Then link with the &
- Select the cell with the value to use
- Link again with &
- Finally, write the rest of the reference with the single quote before the exclamation mark ❗❗❗
And now it works perfectly 😊😎😍