"Gord Dibben" <gorddibbATshawDOTca wrote...
You are not looking to "transpose, but looking to "transfer" A1 from each
worksheet to one column in a summary sheet.

Actually, transposition generalizes as an permutation of coordinates, so it
may be applied to more than 2 dimensions. The OP is recalling this feature
from Lotus 123, which provides 3D transposes (as does Quattro Pro, FWLIW).

One way would be to use a macro to get all sheet names into a list on
summary sheet
....

Or the OP could avoid VBA.

If the other worksheets were grouped together, then the OP could define the
following names referring to the following formulas. Note: for safety in
older versions, this should be done in a new worksheet, which I'll name ' '.

' '!_WSLST:
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","")

WSLST:
=' '!$1:$1

WSOFF:
=MATCH("FirstWorksheet",WSLST,0)-1

Enter the array formula =_WSLST in WSLST, i.e., in ' '!$1:$1. The string
constant "FirstWorksheet" is a placeholder for the name of the first of
these worksheets.

Then you could pull the values of the X99 cells from each of the worksheets
using formulas like the following in cell Y33.

Y33:
=INDIRECT("'"&INDEX(WSLST,ROWS(Y$33:Y33)+WSOFF)&"' !X99")

Fill Y33 down as far as needed.