list data of same cell on different worksheets.

T

Titam

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

Ken Johnson

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
 
K

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
 
C

confused

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
 
K

Ken Johnson

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
 
C

confused

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

BruceN

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")
 
K

Ken Johnson

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
 
Top