Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot insert rows -error message "cannot shift object off sheet" | Excel Discussion (Misc queries) | |||
How to copy or share "Custom Views" from one sheet to another | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
name duplication via "Edit, Move or Copy Sheet" | Links and Linking in Excel |