Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i extract records
how do I extract records on a worksheet showing all female employees who are
older than 26. I have a criteria and an extract area set up on the worksheet. When I go to Data, Filter, then Advanced Filter, I then click on Copy to another location. I put in my information but it lists everyone including the males. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i extract records
You must have the criteria set incorrectly.
Assume: A1:C1 = column headers = name, sex, age A2:An = names B2:Bn = sex = F or M C2:Cn = age as a number Criteria should be: =AND(B2="F",C227) Assume you want this data extracted to an area starting in cell H1. Enter the criteria formula in F2. Select cell A1 Goto DataFilterAdvanced filter Copy to another location List range should already be selected Criteria range: F1:F2 Copy to: H1 OK Biff "karen" wrote in message ... how do I extract records on a worksheet showing all female employees who are older than 26. I have a criteria and an extract area set up on the worksheet. When I go to Data, Filter, then Advanced Filter, I then click on Copy to another location. I put in my information but it lists everyone including the males. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i extract records
Ooops!
Criteria should be: =AND(B2="F",C227) Should be: =AND(B2="F",C226) Biff "T. Valko" wrote in message ... You must have the criteria set incorrectly. Assume: A1:C1 = column headers = name, sex, age A2:An = names B2:Bn = sex = F or M C2:Cn = age as a number Criteria should be: =AND(B2="F",C227) Assume you want this data extracted to an area starting in cell H1. Enter the criteria formula in F2. Select cell A1 Goto DataFilterAdvanced filter Copy to another location List range should already be selected Criteria range: F1:F2 Copy to: H1 OK Biff "karen" wrote in message ... how do I extract records on a worksheet showing all female employees who are older than 26. I have a criteria and an extract area set up on the worksheet. When I go to Data, Filter, then Advanced Filter, I then click on Copy to another location. I put in my information but it lists everyone including the males. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i extract records
Hi Karen
Biff has given you a very good solution which works perfectly. As an alternative, and my own preference, you could set up in column B1 and C1 of your destination sheet, Sex and Age. In B2 enter your required value "f" (without the quotes), in C2 enter 26 In advanced filter, criteria would be Sheet2!$B$1:$C$2 and Destination Sheet2!$A$5:$C$5 That way, you can see exactly what criteria you have set for each column in the range. With 2 columns of criteria it doesn't make a huge difference, but as you expand the criteria for selection it does make it much easier to see exactly what you have selected. You can easily re-run the filter, just changing either of the criteria in B2 or C2. If you name the source range, InsertNameDefineName Mydata Refers to =OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),3) Then in Advanced Filter give the source as MyData. This will prevent Advanced Filter's annoying feature of assuming the source data is on Sheet2, each time you run it. -- Regards Roger Govier "karen" wrote in message ... how do I extract records on a worksheet showing all female employees who are older than 26. I have a criteria and an extract area set up on the worksheet. When I go to Data, Filter, then Advanced Filter, I then click on Copy to another location. I put in my information but it lists everyone including the males. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i extract records
Hi Karen
Forget what I was saying about the named range. I had forgotten that AF doesn't remember the source, even if you do give it a named range. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Karen Biff has given you a very good solution which works perfectly. As an alternative, and my own preference, you could set up in column B1 and C1 of your destination sheet, Sex and Age. In B2 enter your required value "f" (without the quotes), in C2 enter 26 In advanced filter, criteria would be Sheet2!$B$1:$C$2 and Destination Sheet2!$A$5:$C$5 That way, you can see exactly what criteria you have set for each column in the range. With 2 columns of criteria it doesn't make a huge difference, but as you expand the criteria for selection it does make it much easier to see exactly what you have selected. You can easily re-run the filter, just changing either of the criteria in B2 or C2. If you name the source range, InsertNameDefineName Mydata Refers to =OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),3) Then in Advanced Filter give the source as MyData. This will prevent Advanced Filter's annoying feature of assuming the source data is on Sheet2, each time you run it. -- Regards Roger Govier "karen" wrote in message ... how do I extract records on a worksheet showing all female employees who are older than 26. I have a criteria and an extract area set up on the worksheet. When I go to Data, Filter, then Advanced Filter, I then click on Copy to another location. I put in my information but it lists everyone including the males. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract unique records from one column | Excel Worksheet Functions | |||
Using DGET to extract multiple records | Excel Worksheet Functions | |||
Extract multiple records from Excel table | Excel Worksheet Functions | |||
Extract Unique Records from two lists | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions |