Summarize data from multiple sheets

[rgl2sa]

G'day! My first sheet is a summary sheet. All the subsequent sheets are
identically designed data entry sheets, BUT the sheet names are random (not
sequential) AND the names and total number of subsequent data entry sheets
are unknown as additional data entry sheets can be added (and renamed) at any
time. How can I automatically (either by way of formula or macro or other)
reference data from the first data entry sheet into cells in Row-1 of the
summary sheet and then reference the same data from the next data entry sheet
into cells in Row-2 of the summary sheet... continuing until the last sheet?
Merry Christmas!

 

[Luke M]

If you make a list of your sheet names in column A, could do

=INDIRECT("'"&$A2&"'!B$2")

This formula will look at cell B2 in whatever sheet is named in cell A2. You
could then copy across to get different cells from same sheet, or down to
same cells from different sheet.

 

[rgl2sa]

Thanks Luke, that's brilliant and works perfectly... BUT do you know of any
way that I can 'automatically' populate the Sheet Names into column A without
relying on the user to manually add them each time a new sheet is added? The
complete range of Sheet Names can't be pre-populated up front because
additional sheets and their unique random Sheet Names are constantly added on
an ongoing basis.