Advance filter
Posted by siti on April 13, 2001 9:49 PM
i have two columns and about a 100 rows of info. A column is for name of persons the other is their birthdate. I wish to filter only those who were born in a particular year using advance filter method. Any clues? tx in adv, siti
Posted by Dave Hawley on April 13, 2001 10:35 PM
Hi siti
Let's suppose your data is in Columns A and B.
Column B contains your birthdates.
You want to show only those that are born in the year 1962.
Cell B2 contains a date that falls within the year 1962.
Leave cell C1 Blank and in cell C2 put:
=YEAR(B2)=YEAR($B$2)
*Notice the relative and absolute reference of B2.
This should give you a result of TRUE.
Now activate the Advanced Filter and in the "List Range" put: $A$1:$B$200
In the "Criteria Range" put: $C$1:$C$2
Click Ok!
Dave
OzGrid Business Applications
Posted by Mark W. on April 14, 2001 11:13 AM
siti,you can avoid the flawed semantics of Dave's formula posted below by using =YEAR(B2)=1962 instead. Rather than comparing your birthdates against a specified birthyear, Dave's formula, =YEAR(B2)=YEAR($B$2) will compare every record of your data list against the 1st record of the same list. This could be problematic if the ordering of the records is ever changed.
If you'd prefer not to use a constant value (e.g., 1962) in your criteria, then use an absolute reference to a cell that's not within your data list range and enter 1962 there.
Posted by Dave Hawley on April 14, 2001 7:58 PM
Oh Marky :o)
>you can avoid the flawed semantics of Dave's formula posted below by using =YEAR(B2)=1962 instead
This is getting to be just too much fun!
Year(1962) LOL! Did you even try this?
The signs of desperation. Those lessons are still on offer Mark, but I think we should start with Basic Excel instead.
ROFL
Dave
OzGrid Business Applications
Posted by Mark W. on April 15, 2001 12:33 AM
Re: Oh Marky :o)
> Year(1962)...
Who wrote that? Dave are you hallucinating?
Posted by Dave Hawley on April 15, 2001 2:49 AM
Re: Oh Marky :o)
He he, no, It's the tears of laughter getting in my eyes.
BTW how's the VBA going :o) Those lessons are still on offer marky
Dave
OzGrid Business Applications
Posted by siti on April 15, 2001 9:06 AM
heheh
Posted by Dave Hawley on April 15, 2001 2:53 PM
That's entertainment! :o)
Dave
OzGrid Business Applications
