Dear Experts,
I have 28 separate worksheets (classes of drugs) and from column O on each
of those worksheets I want excel to look down the column and detect when
there is text entered (drug name) and record it in a separate sheet. At the
end of the day I want one sheet which collects those occurences of text in
column O and lists them on the Summary Sheet. I should then have one sheet
with certain drug names (meeting a particular criteria) from 28 classes of
drugs.

Column O may have 100 rows but only 16 entries of text. I only want the
text collected.

Is this possible?
regards
Martina

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jc132568" wrote in message
...
Dear Experts,
I have 28 separate worksheets (classes of drugs) and from column O on each
of those worksheets I want excel to look down the column and detect when
there is text entered (drug name) and record it in a separate sheet. At
the
end of the day I want one sheet which collects those occurences of text in
column O and lists them on the Summary Sheet. I should then have one
sheet
with certain drug names (meeting a particular criteria) from 28 classes of
drugs.

Column O may have 100 rows but only 16 entries of text. I only want the
text collected.

Is this possible?
regards
Martina

Try this relatively formulas play which should deliver the expected results ...

Assume 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. Case 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

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. Success? hit
the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"jc132568" wrote:
Dear Experts,
I have 28 separate worksheets (classes of drugs) and from column O on each
of those worksheets I want excel to look down the column and detect when
there is text entered (drug name) and record it in a separate sheet. At the
end of the day I want one sheet which collects those occurences of text in
column O and lists them on the Summary Sheet. I should then have one sheet
with certain drug names (meeting a particular criteria) from 28 classes of
drugs.

Column O may have 100 rows but only 16 entries of text. I only want the
text collected.

Is this possible?
regards
Martina