One way ..

Suppose the source table is in Sheet1, cols A to D, data from row2 down,

In Sheet1
----------
Assuming 2 empty cols to the right, say cols E and F?

Put in F1: =IF(Sheet2!A1="","",Sheet2!A1)

Put in E2:
=IF(A2="","",IF(ISNUMBER(SEARCH($F$1,A2)),ROW(),"" ))

Copy E2 down as many rows as data is expected in the table, say, down to
E100 (Note: Leave E1 empty)

(The extracts will be done in Sheet2)

In Sheet2
----------
A1 is reserved for input of the specific text string / substring
(Input the string / substring into A1)

Paste the col headers over from Sheet1 into A2:D2

Put in A3:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A3 across to D3, fill down to D101
(cover the same range as was done in col E in Sheet1)

Sheet2 will return the required results, all bunched at the top, with blank
rows below

And if you don't want to reproduce col A from Sheet1 in the results, just
change the part " INDEX(Sheet1!A:A, .. " to
" INDEX(Sheet1!B:B, .. " in the formula in A3, then copy across to C3 (i.e.
copy across 1 col less), fill down to C101

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"djm" wrote in message
...
I am looking for a formula to accomplish the following:

I want to search column A of a worksheet for a specific trxt string. If
that
string is found I want to copy the data from that same row to a second
worksheet.
For example I am looking for "Widgets" in column A of Worksheet1. When it
finds "Widgets" in cell A250 I want Excel to copy and paste cells B250,
C250,
D250, etc. into worksheet2.
Does anyone know if there is an easy way to do that?