Date range selection? Anniversary dates
 

I am trying to search a data set with several columns of dates and pull any
date within a range that I type in for the system to search. A small sample
is below.

I want to set it up so I can search once a month or so for people who are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

Use autofilter, from dropdown select custom and greater than or equal to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and pull any
date within a range that I type in for the system to search. A small
sample
is below.

I want to set it up so I can search once a month or so for people who are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

This does not help. I am giving this to people who will not remember how to
even use filter. I am trying to set up a function so they only have to enter
the dates in two cells on a workseet to get the answers.


"Peo Sjoblom" wrote:

Use autofilter, from dropdown select custom and greater than or equal to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and pull any
date within a range that I type in for the system to search. A small
sample
is below.

I want to set it up so I can search once a month or so for people who are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

Good luck, your attitude does not exactly make me want to help you anymore

--
Regards,

Peo Sjoblom


"mrburnette" wrote in message
...
This does not help. I am giving this to people who will not remember how
to
even use filter. I am trying to set up a function so they only have to
enter
the dates in two cells on a workseet to get the answers.


"Peo Sjoblom" wrote:

Use autofilter, from dropdown select custom and greater than or equal to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and pull
any
date within a range that I type in for the system to search. A small
sample
is below.

I want to set it up so I can search once a month or so for people who
are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

Peo,

I am not giving you attitude. I am sure you are a very intelligent person
and I am sure that you want to help based on your fast response. It was
apprciated just not what I need.

"Peo Sjoblom" wrote:

Good luck, your attitude does not exactly make me want to help you anymore

--
Regards,

Peo Sjoblom


"mrburnette" wrote in message
...
This does not help. I am giving this to people who will not remember how
to
even use filter. I am trying to set up a function so they only have to
enter
the dates in two cells on a workseet to get the answers.


"Peo Sjoblom" wrote:

Use autofilter, from dropdown select custom and greater than or equal to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and pull
any
date within a range that I type in for the system to search. A small
sample
is below.

I want to set it up so I can search once a month or so for people who
are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

OK, sorry if I was overreacting.
You could for instance automate the autofilter using VBA so basically all
the users have to do is to type in the 2 dates and press a button


The problem using formulas if indeed the data set is large is that it might
make the workbook slow down to a crawl, you would need fairly complicated
array formulas, something like number 6 from here

http://nwexcelsolutions.com/advanced_function_page.htm

=IF(ROWS(B$1:B1)<=COUNTIF(First!$A$1:$A$20,$A$1),I NDEX(First!$B$1:$B$20,SMALL(IF(First!$A$1:$A$20=$A $1,
ROW(First!$A$1:$A$20)-ROW(First!$A$1)+1),ROWS(B$1:B1))),"")


Now since you want all information you would need to change the INDEX part
to reflect the different columns and use multiple formulas and the SMALL
part would change from (A1:A20=date1)*(A1:A20<=date2)



--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
Peo,

I am not giving you attitude. I am sure you are a very intelligent person
and I am sure that you want to help based on your fast response. It was
apprciated just not what I need.

"Peo Sjoblom" wrote:

Good luck, your attitude does not exactly make me want to help you
anymore

--
Regards,

Peo Sjoblom


"mrburnette" wrote in message
...
This does not help. I am giving this to people who will not remember
how
to
even use filter. I am trying to set up a function so they only have to
enter
the dates in two cells on a workseet to get the answers.


"Peo Sjoblom" wrote:

Use autofilter, from dropdown select custom and greater than or equal
to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and
pull
any
date within a range that I type in for the system to search. A
small
sample
is below.

I want to set it up so I can search once a month or so for people
who
are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year
(ie
1,5,10,15) for anyone in the given data set. Be advised that my
actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005

Date range selection? Anniversary dates
 

Peo,

Ok, now I feel retarded. I have not used VBA at all in since I was in
college in 2002 and can not remember how to use it. How eaxctly do I automate
the autofilter?

"Peo Sjoblom" wrote:

OK, sorry if I was overreacting.
You could for instance automate the autofilter using VBA so basically all
the users have to do is to type in the 2 dates and press a button


The problem using formulas if indeed the data set is large is that it might
make the workbook slow down to a crawl, you would need fairly complicated
array formulas, something like number 6 from here

http://nwexcelsolutions.com/advanced_function_page.htm

=IF(ROWS(B$1:B1)<=COUNTIF(First!$A$1:$A$20,$A$1),I NDEX(First!$B$1:$B$20,SMALL(IF(First!$A$1:$A$20=$A $1,
ROW(First!$A$1:$A$20)-ROW(First!$A$1)+1),ROWS(B$1:B1))),"")


Now since you want all information you would need to change the INDEX part
to reflect the different columns and use multiple formulas and the SMALL
part would change from (A1:A20=date1)*(A1:A20<=date2)



--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
Peo,

I am not giving you attitude. I am sure you are a very intelligent person
and I am sure that you want to help based on your fast response. It was
apprciated just not what I need.

"Peo Sjoblom" wrote:

Good luck, your attitude does not exactly make me want to help you
anymore

--
Regards,

Peo Sjoblom


"mrburnette" wrote in message
...
This does not help. I am giving this to people who will not remember
how
to
even use filter. I am trying to set up a function so they only have to
enter
the dates in two cells on a workseet to get the answers.


"Peo Sjoblom" wrote:

Use autofilter, from dropdown select custom and greater than or equal
to
07/01/1985,
AND
less than or equal to 07/31//1985


--
Regards,

Peo Sjoblom




"mrburnette" wrote in message
...
I am trying to search a data set with several columns of dates and
pull
any
date within a range that I type in for the system to search. A
small
sample
is below.

I want to set it up so I can search once a month or so for people
who
are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year
(ie
1,5,10,15) for anyone in the given data set. Be advised that my
actual
data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005