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.

Worksheet with country information

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 😎 )

How to use the cell's contain in a reference

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 😱😱😱

Connect each cell to the other worksheet

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"

Detail of a reference in relationship with a cell in another worksheet

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.


INDIRECT example

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.

  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 part with the symbol &

In this example, we will write our function like this 


Use the value of a cell to build your custom reference with INDIRECT

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 😀😍😍

Result of a dynamic range build with INDIRECT

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).

INDIRECT is 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.

='United Kingdom'!B2

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

  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 the single quote before the exclamation mark ❗❗❗


Writing of a reference with space in the function indirect

And now it works perfectly 😊😎😍

Related posts

Permanent link to this article:

Leave a Reply

Your email address will not be published.