With Excel365 and flexibility to add formulas to each sheet at a specific location, I have managed this by adding

=TEXTAFTER(CELL("filename",A1),"]")

to A1 to get the sheetname and then using the TOCOL formula in my summary sheet to pull through all the A1 cells: something like

=TOCOL('[FirstSheetName]:[LastSheetName]'!A1)

Seems to manage adding/deleting/renaming sheets, but I haven't tested it very robustly.