copy specific rows using "IF" to another sheet

  • Thread starter Thread starter Henry
  • Start date Start date
H

Henry

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
 
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.
 
Why not just add column(s) and then use filter>autofilter
name family friend
joe x
bill
molly x
 
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 ..
 
Back
Top