You can easily list sheet index numbers in MS Excel by using the SHEET and INDIRECT functions. The functions can be applied by first giving excel sheets respective names and then use this simple formula to list the index numbers:
“ = SHEET (INDIRECT (name & “!A1”) ) ”
Figure.1: List sheet index numbers
In the above example, the same formula is used for the data in C5 which is as below:
= SHEET ( INDIRECT (B5 & A1”) )
Similarly, each of the sheets has different relative formula on the basis of which index numbers in excel sheet are determined. Different relevant formulas can be seen in the figure above.
The INDIRECT function is used to evaluate text as the legitimate reference. In the figure above, the names of the sheets are being taken from column B while A acts as an exclamation point with which B is concatenated
Than the INDIRECT function is used again to convert the text “SHEET !A1” into a legitimate reference which is then entered into the SHEET function.
The SHEET function than derives the current index for relative sheets according to their listing.