Excel-Exercise-Free

1. The INDIRECT function gives you the possibility to create links with tabs by using the name of a tab like parameter in the link. If we take the following example, we will use the label of the column to return the corresponding value of each tab

Formule de la cellule

2. Most of Excel's users create a formula like these one but it absolutly not convenient when you have several worksheets.

3. The idea is to replace the name of the worksheets (or the tabs) by their value in column A. To do that we are going to use the INDIRECT function. The contains of this function must be write between double-quote. In this case, the result of the function is the same.

Formule de la cellule

4. Now, we keep the reference to the cell B7 between double-quote (with the symbol ! ) and we add the contain of the cell A2 to build the path for the cell in the worksheet

Formule de la cellule

5. And now when you copy this formula according with the contain of the cells of the column A, you return the same result as the point 2.

6. More interesting but more complicated, we want to use the contain of the headline (in row 1). In this case, it's possible to specify which data we want to return.
First, we make a classical VLLOKUP function.

Formule de la cellule

7. And then, we use the INDIRECT function where there is a reference to the worsheet FR in the formula.

Formule de la cellule

8. Now, whatever the value in the headline, you display the contain of all the other worsheets.
Caution: It is compulsory to have the same structure of data in every worsheets.