how do i extract records

[Guest]

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.

 

[T. Valko]

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",C2>27)

Assume you want this data extracted to an area starting in cell H1.

Enter the criteria formula in F2.

Select cell A1
Goto Data>Filter>Advanced filter
Copy to another location
List range should already be selected
Criteria range: F1:F2
Copy to: H1
OK

Biff

 

[T. Valko]

Ooops!

Criteria should be:
=AND(B2="F",C2>27)

Should be:

=AND(B2="F",C2>26)

Biff

 

[Roger Govier]

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, Insert>Name>Define>Name 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.

 

[Roger Govier]

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.