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