The Excel INDIRECT function will help you to 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 a lot of worksheets, one per 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 in another worksheet.

AVOID THIS MISTAKE

In a basic approach, basic Excel users will connect the content of cell B2 for each worksheet manually. 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 detail of the reference is

• 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

So the idea is to replace the worksheets' reference (UK, Italy, France, Spain, ...) with 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.

=INDIRECT("UK!B2")

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 column A. And this is very easy.

1. Keep between double quotes the part of the reference that doesn't change (here !B2 with the exclamation mark)
2. Out of the double quotes, you select the value that you want to include in your reference (the reference of the cell A2)
3. 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 have such an 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.

='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 the following

1. We must start by the quote between 2 double-quotes "'"
2. Then link with the &
3. Select the cell with the value to use
4. Link again with &
5. Finally, write the rest of the reference with a single quote before the exclamation mark !!!!

=INDIRECT("'"&A2&"'!B2")

And now it works perfectly 😊😎😍