Max,
Thanks for the quick response. I loaded these formulas into a sample sheet
but only had luck with the formula on sheet A. Sheet B formula did not pull
in any data. When I enter the formula EXCEL changes it and asks that I update
the file. Not sure why?
I can send you the sample sheet for reference if you like. Any ideas

Thanks,
Trese

"Max" wrote:

One way which should do it ..

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )

Copy D1 down to say, D100, to cover the max expected data range in col A
(can copy down ahead of expected data in col A)

In SheetB
----
Put in B1:

=IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL
(SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0)))

Copy B1 down to B100
(cover the same range size as in col D in SheetA)

Col B will return the desired results from SheetA's col B,
all neatly bunched at the top ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Good day all, hope you can help!!

I need a formula that will take info from sheet A column b only if sheet A
column A contains "EMC" somewhere in the text. I need it to paste into
Sheet
B coumn B exactly as shown in sheet A column b as well as index to the
next
line that contains this info.

example:
Sheet A
A B
EMC, Safe PREPARE Checklist
ENVIR Send PCB for test
ID, EMC, SAFE Testing files complete

Sheet B:
A B
PREPARE Checklist
Testing files complete

I have tried using the following formula but I get empty lines "FALSE"
between the actual info and it is not pulling the information exactly as
it
is on the other sheet. It is also not pulling all info from lines that
contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does
not
pull that line item.
=IF(Sheet!A6="EMC",Sheet!B6)

Help, what am I doing wrong? I am a newbie and have spent several hours
looking through all the formulas/discussions you have provided to other
users
but did not see one that would do what I am looking for on this sheet.
Your help would be appreciated.

Thanks,
Trese