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