I am looking to see how I would go about creating a formula that would copy
the whole row, if the value is true. So if the value of F4 is "Jim" it would
copy the whole ro, and paste it to a seperate sheet.

"Scott" wrote
I am looking to see how I would go about
creating a formula that would copy the whole row,
if the value is true. So if the value of F4 is "Jim" it would
copy the whole ro, and paste it to a seperate sheet

Here's one set-up to try ..

In Sheet1
------------
Assume the sample table below is in cols A to F, data from row2 down, with
the key column being col F: "Assigned to", which houses the names of the
persons the cases are assigned to,
viz.: Jim, Peter, Mary

LastN FirstN Case# Desc1 Desc2 Assigned to
Nolan Kevin 1111 Text1 Text11 Jim
Martin Peter 1112 Text2 Text12 Peter
Hayes John 1113 Text3 Text13 Mary
Factor Max 1114 Text4 Text14 Peter
Kenton Joey 1115 Text5 Text15 Jim
Kenyon Long 1116 Text6 Text16 Mary
etc

List across in empty cols to the right, say, H1:J1
the 3 persons assigned: Jim, Peter, Mary

Put in H2: =IF($F2="","",IF($F2=H$1,ROW(),""))

Copy H2 across to J2, then fill down by a safe "max"
number of rows that data is ever expected in cols A to F,
say, down to J100 ?

In a new sheet named: Jim
------------------------------*---------
Let's reserve cell A1 to pull in the sheetname

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: Jim into A1.
But you need to save the file first)

Copy Paste the same col headers from Sheet1
into A2:E2, i.e.: LastN FirstN Case# Desc1 Desc2

Put in A3:

=IF(ISERROR(SMALL(OFFSET(Sheet1!$G:$G,0,MATCH($A$1 ,Sheet1!$H$1:$K$1,0)),ROWS
($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(OFFSET (Sheet1!$G:$G,0,MATCH($A$1
,Sheet1!$H$1:$K$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1 !$G:$G,0,MATCH($A$1,Sheet1
!$H$1:$K$1,0)),0)))

Note: You'd need to correct / restore the couple of inevitable line wraps /
line breaks when you copy paste the above formula into A3

Copy A3 across to E3, fill down by as many rows as was done in Sheet1, viz.
down to E101

You'll see that cols A to E (in row3 down)
will auto-return the "filtered" rows for "Jim" from Sheet1,
i.e. for the sample data-set above, it'll appear as:

Jim
LastN FirstN Case# Desc1 Desc2
Nolan Kevin 1111 Text1 Text11
Kenton Joey 1115 Text5 Text15
(rest are blank rows)

Now, just duplicate / make a copy of the sheet: Jim
rename it as: Peter
and you'll get the "filtered" rows for "Peter"

Peter
LastN FirstN Case# Desc1 Desc2
Martin Peter 1112 Text2 Text12
Factor Max 1114 Text4 Text14
(rest are blank rows)

Repeat the sheet duplication / renaming
for the remaining "Mary"
which yields:

Mary
LastN FirstN Case# Desc1 Desc2
Hayes John 1113 Text3 Text13
Kenyon Long 1116 Text6 Text16
(rest are blank rows)

Data entered into Sheet1 will automatically appear
in the correct assignee's indivdual sheet

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

THANKS FOR THE HELP

You're welcome, Scott
Thanks for the postback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Scott" wrote in message
...
THANKS FOR THE HELP