Hi Titam

If there are a few sheets you can create the links yourself
=Sheet2!A1

You can also use this macro to do it
http://www.rondebruin.nl/summary.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Titam" wrote in message ...
I have a text data stored in same cell of different worksheets on the same
file. I need to list them down in a single fresh worksheet. Is there any
formula or any way that I may use?

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

Hi Titam,

I just realised it can be reduced from using two columns to just one:

Sheet1!A2 has the following formula

=INDIRECT("Sheet" & ROW() & "!"& $A$1)

Sheet1!A1 still contains the address of the text cell that is in the
same position on every sheet.

The only drawback I can see is that when the common text cell is a
blank cell the summary on sheet1 will be a "0" (zero) instead of blank.

Ken Johnson