Collating entries from multiple sheets

[jc132568]

Dear Experts,
I have 28 worksheets (drug classes) each with a column O in which drug names
will appear if they meet certain criteria. I would like to list these drugs,
from the 28 worksheets on one page, as a summary. I want excel to look down
column O, for about 100 rows, and collect any occurrences of drug names. In
100 rows drug names may appear 20 times (they will all be unique), the other
cells being blank. Can I collect these occurrences on one sheet for summary
purposes?

regards
Martina

 

[ryguy7272]

This should give you some ideas:
http://www.rondebruin.nl/copy2.htm

Also:
http://www.contextures.com/xlvba01.html
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

HTH,
Ryan---

 

[sajay]

this can be done with pivot table
or simply few worksheet functions. like a row below column 'O' for counting
occurances. sorting all sheets except summary togather. etc
better if you can post the file it is easier and better.

 

[Max]

This relatively simple formulas play should deliver the expected results ...
As you posted, target data is within rows 2 to 100
in col O in each of the 28 drug sheets

In the summary sheet,
List the 28 actual sheetnames for the drugs into AD1:BE1
(A once-off effort, take care to ensure that there's no typos in the
listing. The names listed must match exactly with what's on the tabs except
for case, which is immaterial)

Put in A2:
=IF(ISTEXT(INDIRECT("'"&AD$1&"'!O"&ROWS($1:1)+1)),ROW(),"")
Copy A2 across by 28 cols to AB2, fill down to AB100
[You can hide away/ minimize the criteria cols A to AB]

Then place in AD2
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(INDIRECT("'"&AD$1&"'!O:O"),SMALL(A:A,ROWS($1:1))))
Copy AD2 across to BE2, fill down to BE100*. The expected TEXT results from
each drug's sheet's col O will appear neatly packed at the top. Voila? hit
the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik