Function INDIRECT

The INDIRECT function helps you to create a dynamic references of your cells. In other words, you can replace a part of a reference by a variable. Let's see that through 2 examples

 

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. This will be our golden thread.

 

 

 

 

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

It's the worst you can do 😱😱😱

 

 

 

Let's focus on the references

Excel referenceAs 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.

In this situation, the INDIRECT function is compulsory.

The same reference with INDIRECT

INDIRECT exampleIf you write the reference of a cell in an INDIRECT function, you must write the reference between double quotes.

=INDIRECT("FR!B8")

Have you seen the result in the cell is exactly the same with the function INDIRECT 😎😍🤗

Customize your reference

Now, we need to customize the parameter of the INDIRECT function to "read" the cells of the column A. A. And this is very easy.

  • You keep between double quotes the part of the reference that doesn't change
  • Out of the double quotes, you select the value that you want to include in your reference
  • 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 SIMPLE 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.