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 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

if its exactly the same data you could just right click on the tab and Copy
the whole page (right click - copy,, make sure the tick in in create a copy)
will make a duplicate of the page wittout the need for formulas or macros

Hi confused,
If on sheet1 you want a list of all the text values that are in say A1
on all of the other sheets I don't think copying sheets will produce
it.

Ken Johnson

possibly not but that depens on wether the "same data" is just a template
idea for new pages,,
"Ken Johnson" wrote:

Hi confused,
If on sheet1 you want a list of all the text values that are in say A1
on all of the other sheets I don't think copying sheets will produce
it.

Ken Johnson

I hope I have undaerstood you want
with control down clikc all the sheet tabls.
now type entry in A1 of the sheeet open.
this is entered in all sheets A1.
deselect sheeets by clicking any sheet tab.




"confused" wrote in message
...

possibly not but that depens on wether the "same data" is just a template
idea for new pages,,
"Ken Johnson" wrote:

Hi confused,
If on sheet1 you want a list of all the text values that are in say A1
on all of the other sheets I don't think copying sheets will produce
it.

Ken Johnson

Thanks to all who answered this post. Using your information plus that in
the posting for indirect reference(again?) I was able to prepare a group of
worksheets (one for each day a cafe was open) each of which was a worksheet
of the day's register activity. I was then able to create a summary
worksheet which has a single line for the pertinant information gathered from
each of the subsequent worksheets. Each daily sheet is tabbed as "May 24",
"May 25", etc. I created two columns on the left of my summary sheet, one
for the month and one for the day of the month. (in columns "A" and "B"). I
then used the following for the cell in the summary sheet: =INDIRECT("'"&
$A10 &" " &$B10 & "'!$D$19")


"Titam" wrote:

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 BruceN,
Well done, I've managed to reproduce your technique and it produces a
summary of each day-sheet's D19 entry in combination with the date in
columns A and B. Very useful for people with lots of sheets with common
information in the same cell.

Ken Johnson