The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1:
=B5&"!A1" ="Sheet1"&"!A1" ="Sheet1!A1"
The INDIRECT function then coerces the text "Sheet1!A1" into a valid reference, which is passed into the SHEET function.
The SHEET function then returns the current index for each sheet as listed.