Function INDIRECT

The INDIRECT function helps you to create dynamic references. In other words, you can replace a part of a reference by a variable.

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.

You want to create a worksheet that return the calling code for each country.

As you can notice, the value in the column A is the same that the worksheet name.

We are going to use the content of the column A to build the references.

Don't link each cell one by one

In a basic approche, you will select the contain of the cell B8 for each worksheet.

It's the worst that you can do 😱😱😱

Let's focus on the references

Excel reference

As you can noticed, the writing of a reference is

SheetName!ReferenceCell

So the idea is to replace the worksheets' reference (FR, BE, IT, ...) by the values of the column A.

Build the dynamic reference

First insert your reference in the INDIRECT function

With the INDIRECT function, the references must be written as text so between double-quotes.

=INDIRECT("FR!B8")

INDIRECT example

And the result is exactly the same (good point) 😎😍🤗

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
  2. Out of the double quotes, you select the value that you want to include in your reference
  3. You link both part with the symbol &

In our example, we will write our function like this 

=INDIRECT(A2&"!B8")

Let's analyze the function

  • A2 is the value of the cell A2 ; FR
  • We use the & to link this reference with the invariant part
  • The invariant part (the one that is always the same) is writing between double quotes

Be careful with the spaces

If the name of your worksheet has blank (or space), the worksheet's name will be write between simple quotes.

Here we have 'United States' for the sheet's name

WE MUST KEEP THE QUOTES !!!

In that case, you must keep the symbol "simple quote" inside your INDIRECT function.

So we write the function like this

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

And it works 😊😎😍

To clarify, the writing, the first part of the function is double-quote/simple-quote/double-quote.

Example with the INDIRECT function

In the following video, you will see and example with the INDIRECT function to extract randomly data from a list.

Permanent link to this article: https://www.excel-exercise.com/function-indirect/


Leave a Reply

Your email address will not be published.