How to use the INDIRECT function of Excel?

How to use the INDIRECT function of Excel?
Last Updated on 12/06/2023
Reading time: 3 minutes

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

How to use the cell's contain in a reference

In each sheet, you have information about each country (like Capital, Currency Code, ...)

Data about countries

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 😱

Reference to other worksheets manually

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"

Reference of cell in a worksheet

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

Reference of the cells as text

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

Use the contain of a cell in a function INDIRECT

Step 3: Copy-Paste your formula

We copy-paste this formula to the rest of our document and the job is finished 😀😍

Construction of dynamic references with INDIRECT

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

REF Error with INDIRECT

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

Build dynamic Reference with spaces with INDIRECT

And now it works perfectly 😊😎😍

Leave a Reply

Your email address will not be published. Required fields are marked *

How to use the INDIRECT function of Excel?

Reading time: 3 minutes
Last Updated on 12/06/2023

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

How to use the cell's contain in a reference

In each sheet, you have information about each country (like Capital, Currency Code, ...)

Data about countries

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 😱

Reference to other worksheets manually

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"

Reference of cell in a worksheet

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

Reference of the cells as text

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

Use the contain of a cell in a function INDIRECT

Step 3: Copy-Paste your formula

We copy-paste this formula to the rest of our document and the job is finished 😀😍

Construction of dynamic references with INDIRECT

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

REF Error with INDIRECT

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

Build dynamic Reference with spaces with INDIRECT

And now it works perfectly 😊😎😍

Leave a Reply

Your email address will not be published. Required fields are marked *