Looking to reference a specific cell in all of the worksheets and display the results in separate cells on one summary sheet …?
Enter the “sheet name” in a cell & use the fill handle to drag across all sheet names (however they are named – so long as you have the tab names in a list of cells).
Assuming these are in A1:A30, then in B1, enter:
=INDIRECT(“‘”&A1&”‘!E10”), assuming E10 is the repeating cell in each sheet, and fill this across. (That’s an apostrophe inside the quotes.)