Hi Titan,
If you don't want to use a macro and there are too many sheets, or the
number of sheets could change, then, assuming sheet1 is where the
summary is to be viewed:
1) Type the address of the common cell that contains the text into A1
on sheet1 eg if the text is in B5 on every sheet then just type B5 into
A1 on sheet1.

2)type the following into A2 on sheet1 = "Sheet" & row() & "!"

3)type the following into B2 on sheet1 = INDIRECT(A2)

4)Select A2:B2 on sheet1 and fill their formulas down for as many rows
as there are sheets in the workbook. If you ever have to add another
sheet just fill these formulas down 1 more row

Ken Johnson