"T. Valko" wrote...
....
Entered in B1 and copied down as needed:

=INDEX(INDIRECT(A$1&"!B5:B100"),ROWS(B$1:B1))
....

Ugh! That's a technical assessment.

If the OP wants the value from cell B1 in the worksheet named in cell
A1 in the current worksheet in cell B1 in the current worksheet, more
general to use

=INDIRECT("'"&$A$1&"'!RC",0)

Rule-of-thumb: if you need to use INDIRECT for relative references,
there's NEVER a good reason to use A1-style referencing in INDIRECT's
first argument.