The Excel INDIRECT function helps you customize your cell's references. This is the best way to create dynamic references.
A situation where the function INDIRECT is very useful in Excel
Let's say you have a workbook with numerous worksheets, one for each country. In each sheet, you have information about each country (like Capital, Currency Code, ...)
We want to return all the capital names for each country to another worksheet.
AVOID THIS MISTAKE
In a basic approach, basic Excel users will manually connect the content of cell B2 for each worksheet. It's the worst that you can do 😱
Let's analyse a reference.
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 column A"
The details of the reference are
- First, position the Sheet Name
- The second information, the exclamation mark sign, splits the sheet name and the reference of the cell
- Third, we have the reference of the cell.
Build the dynamic reference.
The idea is to replace the worksheets' references (UK, Italy, France, Spain, ...) with the values in column A.
Step 1: Write your reference in the INDIRECT function
With the INDIRECT function, the references must be written as text, so between double-quotes.
=INDIRECT("UK!B2")
And the result is 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 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 cell A2)
- You link both parts with the symbol &
In this example, we will write our function like this =INDIRECT(A2&"!B2")
Step 3: Copy-Paste your formula
We copy-paste this formula to the rest of our document, and the job is finished 😀😍
Be careful with the spaces.
If some of your worksheet names have spaces in their name, the previous formula will return an error #REF (not able to understand the reference).
When you encounter such an error, don't try to correct the formula; instead, return to the basics by creating a connection with the worksheet to understand the writing.
='United Kingdom'!B2
If the name of your worksheet has a blank (or space), the worksheet's name will be written between simple quotes 😮 AND WE MUST KEEP THE QUOTES in the INDIRECT formula!!!
So, the writing is as follows.
- We must start with 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 a single quote before the exclamation mark !!!!
=INDIRECT("'"&A2&"'!B2")
And now it works perfectly 😊😎😍
