copy specific rows using "IF" to another sheet
 

I have created an address book including family and friends.
At the right side of the sheet I have created 2 additional columns labeled
"family", "friends". Within the cells of these columns I have placed an "x"
as an identifier of that row having either "family" or "friend" address
content.
How do I use the IF command in this case. ie: IF cell value = x then send
that row to another sheet in the workbook? I am assuming that I need to
format the subsequent sheets to match that of the master sheet. My goal is
to have created 2 additional worksheets. One for "Family" and one for
"Friends".

My first question on the post,
Thanks for any help,

Henry

copy specific rows using "IF" to another sheet
 

A formula doesn't "send" data to a distant cell. A formula gives rise a
result in the cell in which it is placed.
If your family column is H, for example, then in A2 on your family sheet,
use =IF($H2="x",IF(Sheet1!A2="","",Sheet1!A2),"") and copy across to columns
B, C, etc. and down as many rows as required.
--
David Biddulph

"Henry" wrote in message
...
I have created an address book including family and friends.
At the right side of the sheet I have created 2 additional columns labeled
"family", "friends". Within the cells of these columns I have placed an
"x"
as an identifier of that row having either "family" or "friend" address
content.
How do I use the IF command in this case. ie: IF cell value = x then send
that row to another sheet in the workbook? I am assuming that I need to
format the subsequent sheets to match that of the master sheet. My goal
is
to have created 2 additional worksheets. One for "Family" and one for
"Friends".

My first question on the post,
Thanks for any help,

Henry

copy specific rows using "IF" to another sheet
 

Why not just add column(s) and then use filterautofilter
name family friend
joe x
bill
molly x


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Henry" wrote in message
...
I have created an address book including family and friends.
At the right side of the sheet I have created 2 additional columns labeled
"family", "friends". Within the cells of these columns I have placed an
"x"
as an identifier of that row having either "family" or "friend" address
content.
How do I use the IF command in this case. ie: IF cell value = x then send
that row to another sheet in the workbook? I am assuming that I need to
format the subsequent sheets to match that of the master sheet. My goal
is
to have created 2 additional worksheets. One for "Family" and one for
"Friends".

My first question on the post,
Thanks for any help,

Henry

copy specific rows using "IF" to another sheet
 

One easy formulas play which should deliver it for you ..

Assume you have this listed in a sheet: M
cols A to E, data from row2 down

Name Address Tel# Family Friends
Name1 Add1 Tel#1 x
Name2 Add2 Tel#2 x
Name3 Add3 Tel#3 x
Name4 Add4 Tel#4 x
Name5 Add5 Tel#5 x
Name6 Add6 Tel#6 x
Name7 Add7 Tel#7 x
etc

In another sheet: Family,
Copy n paste the col labels from M's A1:C1
into B1:D1, viz: Name, Address, Tel#

Put in A2:
=IF(M!D2="x",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(M!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
data in M, say down to D100? Cols B to D will return the required results
from M (re-below), ie only those lines marked "x" in M's col D ("Family"),
with all lines neatly bunched at the top. Hide away col A if desired.

Name Address Tel#
Name1 Add1 Tel#1
Name3 Add3 Tel#3
Name4 Add4 Tel#4
Name7 Add7 Tel#7

Now, just make a copy of the sheet "Family", rename it as: Friends
Then amend the formula in A2 to point to M's col E ("Friends"),
ie make it in A2 as:
=IF(M!E2="x",ROW(),"")
Copy A2 down to the same extent and you'll get the result lines for
"Friends" (re-below). No change is required to the formulas in cols B to D.

Name Address Tel#
Name2 Add2 Tel#2
Name5 Add5 Tel#5
Name6 Add6 Tel#6

Adapt & extend to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Henry" wrote:
I have created an address book including family and friends.
At the right side of the sheet I have created 2 additional columns labeled
"family", "friends". Within the cells of these columns I have placed an "x"
as an identifier of that row having either "family" or "friend" address
content.
How do I use the IF command in this case. ie: IF cell value = x then send
that row to another sheet in the workbook? I am assuming that I need to
format the subsequent sheets to match that of the master sheet. My goal is
to have created 2 additional worksheets. One for "Family" and one for
"Friends".

My first question on the post,
Thanks for any help,

Henry