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.

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.

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.

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.

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.