Create summary sheet

You may use in C4:

Formula:

Please Login or Register  to view this content.

in D4:

Formula:

Please Login or Register  to view this content.

in E4:

Formula:

Please Login or Register  to view this content.

and so on

and copy down

with indirect function:

C4:
=INDIRECT("'"&$B4&"'!"&"D4")

C4 =
=INDIRECT("'"&$B4&"'!"&"D5")

E4 and across:
=INDIRECT("'"&$B4&"'!"&CELL("address",G$8))