The INDIRECT function gives you the possibility to create a relation between your worksheet's name and your cell's reference containing your worksheet's name.


In the opposite example, we want to return the calling code for each countries of the column A. You can see that the data in column A is exactly the same as the name of worksheets.







If you want to fill the column B, most of the Excel's users will create a formula where they pointed each cell B8 of any worksheet. This method is absolutely not convenient, especially when you have a lot of worksheets.





So the idea is to replace the worksheets' name (FR, BE, IT, ...) by the values of the column A. In this situation, the INDIRECT function is compulsory.


The INDIRECT function works only if its single parameter is a string. So the reference of your cell must be write between double quote like this example.



With this writing, the result in your cell is the same. You still return the contain of the cell B7 of the worksheet FR.

Now, let's see how is build a reference between tabs. First, you have the name of your worksheet and then a exclamation mark. This symbol split the name of the worksheet from the cell's reference. And to finish, you have the reference of the cell.

Applied to our problem, we want to change the name of the worksheet to refer to values ​​in column A. So we transform our formula keeping in quotes invariant elements (which do not move) and we will remove elements that will vary (column A).

The variable will be equal to FR, ES, IT, .... and the fixed part is the rest ; either! "B8" in our example. The formula in B2 is therefore now be written:



We have, outside of quotes, the variable part. And between quotes, the reference and the exclamation mark. Note the & symbol between the two parts. The symbol & concatenate (or link) different elements in a formula.

In this Excel spreadsheet, copy the previous formula in column B to display the calling code store in the other worksheets of your workbook.









If the name of your worksheet has blank (or space), you can see that the worksheet's name is rounded by quote. You can see in the following example that the worksheet's name contain blanks, so your INDIRECT function must be written like this.




Because you must keep the quote around the variable, you start your argument with the first quote rounded by 2 double-quotes, then the contain of the cell A2, and to finish, the fixing part of the reference but with a quote at the beginning instead of an exclamation mark.

Related articles

Have a look at these other articles that could help you in your work

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

Leave a Reply

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