Creating a Table of Figures in Excel across multiple sheets

for doing this within a workbook - you probably want to use the indirect function

so you need the sheet names (maybe make them something you can easily fill down or calculate)
then something like

Please Login or Register  to view this content.

as per the example here

https://exceljet.net/excel-functions...irect-function

to pull the data in A1 (or wherever else on that sheet) into the table. Have a go and then if you have trouble we might need a cut down the example file.

if you want to do it between workbooks we might move to vba.