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?

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?

you dont say how you get the data from to do the search but if you are
searching from a1 on sheet two as max has said so that a1 has the text in it
that you want then in cell B2=Vlookup(A1,database range,2,false),this returns
your column B, column c cell C1 =vlookup(A1,database range,3,false),column d
cell D1 = vlookup(A1,database range,4,false) etc etc
--
paul
remove nospam for email addy!



"Max" wrote:

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?