Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list option at
C1 upon selecting March, I want the list in D1 to be reflecting the options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list option at
C1 upon selecting March, I want the list in D1 to be reflecting the options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

Sure am delighted to hear from you after a long time Max!

I am sorry but I think I wasn't clear enough.

What I meant was that list option of "Data Validation" is required not auto
filter. I want cell C1 to be reflecting a combo box reflecting Name of months
Jan to Dec. Upon selecting a month the same kind of Data Validation combo box
list in D1 based on a custom formula or some other way to present the dates
pertaining to such a month only from the source of Column B containing a list
of mix dates.

Hopefully a supportive feedback is replied

Thanx

--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list option at
C1 upon selecting March, I want the list in D1 to be reflecting the options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

You'd have to extract all the dates for that month to form another list then
use that extracted list as the source for the drop down list.

With your list of dates in B1:B5 enter this array formula** in K1 and copy
down to K10:

=IF(ROWS(K$1:K1)<=SUMPRODUCT(--(TEXT(B$1:B$5,"mmmm")=C$1)),INDEX(B$1:B$5,SMALL(IF (TEXT(B$1:B$5,"mmmm")=C$1,ROW(B$1:B$5)-MIN(ROW(B$1:B$5))+1),ROWS(K$1:K1))),"")

Create this named formula (dynamic range for the extracted dates):

InsertNameDefine
Name: List2
Refers to:

=Sheet1!$K$1:INDEX(Sheet1!$K$1:$K$10,COUNT(Sheet1! $K$1:$K$10))

OK

Select cell D1
Set up a data validation list.
As the source use this formula:

=IF(COUNT(List2),List2,NA())

Format cell D1 as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Sure am delighted to hear from you after a long time Max!

I am sorry but I think I wasn't clear enough.

What I meant was that list option of "Data Validation" is required not
auto
filter. I want cell C1 to be reflecting a combo box reflecting Name of
months
Jan to Dec. Upon selecting a month the same kind of Data Validation combo
box
list in D1 based on a custom formula or some other way to present the
dates
pertaining to such a month only from the source of Column B containing a
list
of mix dates.

Hopefully a supportive feedback is replied

Thanx

--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired
functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list
option at
C1 upon selecting March, I want the list in D1 to be reflecting the
options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

Forgot something:

Format K1:K10 as DATE


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You'd have to extract all the dates for that month to form another list
then use that extracted list as the source for the drop down list.

With your list of dates in B1:B5 enter this array formula** in K1 and copy
down to K10:

=IF(ROWS(K$1:K1)<=SUMPRODUCT(--(TEXT(B$1:B$5,"mmmm")=C$1)),INDEX(B$1:B$5,SMALL(IF (TEXT(B$1:B$5,"mmmm")=C$1,ROW(B$1:B$5)-MIN(ROW(B$1:B$5))+1),ROWS(K$1:K1))),"")

Create this named formula (dynamic range for the extracted dates):

InsertNameDefine
Name: List2
Refers to:

=Sheet1!$K$1:INDEX(Sheet1!$K$1:$K$10,COUNT(Sheet1! $K$1:$K$10))

OK

Select cell D1
Set up a data validation list.
As the source use this formula:

=IF(COUNT(List2),List2,NA())

Format cell D1 as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Sure am delighted to hear from you after a long time Max!

I am sorry but I think I wasn't clear enough.

What I meant was that list option of "Data Validation" is required not
auto
filter. I want cell C1 to be reflecting a combo box reflecting Name of
months
Jan to Dec. Upon selecting a month the same kind of Data Validation combo
box
list in D1 based on a custom formula or some other way to present the
dates
pertaining to such a month only from the source of Column B containing a
list
of mix dates.

Hopefully a supportive feedback is replied

Thanx

--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired
functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list
option at
C1 upon selecting March, I want the list in D1 to be reflecting the
options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

Hopefully a supportive feedback is replied
Well, I did start off with: One simple alternative ..
Trouble is, the option did not appeal to you,
not that my response wasn't supportive.

Anyway, think Biff gave you the detailed construct which is required. Imo,
it takes a fair bit of work to "emulate" what autofilter does so well
natively.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanx!

Anyway how to contract the list so as to reflect only the visible values in
K1:K10? As the blanks, i.e. "" are also shown!

Thanx!

--

Best Regards,
FARAZ A. QURESHI


"T. Valko" wrote:

Forgot something:

Format K1:K10 as DATE


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You'd have to extract all the dates for that month to form another list
then use that extracted list as the source for the drop down list.

With your list of dates in B1:B5 enter this array formula** in K1 and copy
down to K10:

=IF(ROWS(K$1:K1)<=SUMPRODUCT(--(TEXT(B$1:B$5,"mmmm")=C$1)),INDEX(B$1:B$5,SMALL(IF (TEXT(B$1:B$5,"mmmm")=C$1,ROW(B$1:B$5)-MIN(ROW(B$1:B$5))+1),ROWS(K$1:K1))),"")

Create this named formula (dynamic range for the extracted dates):

InsertNameDefine
Name: List2
Refers to:

=Sheet1!$K$1:INDEX(Sheet1!$K$1:$K$10,COUNT(Sheet1! $K$1:$K$10))

OK

Select cell D1
Set up a data validation list.
As the source use this formula:

=IF(COUNT(List2),List2,NA())

Format cell D1 as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Sure am delighted to hear from you after a long time Max!

I am sorry but I think I wasn't clear enough.

What I meant was that list option of "Data Validation" is required not
auto
filter. I want cell C1 to be reflecting a combo box reflecting Name of
months
Jan to Dec. Upon selecting a month the same kind of Data Validation combo
box
list in D1 based on a custom formula or some other way to present the
dates
pertaining to such a month only from the source of Column B containing a
list
of mix dates.

Hopefully a supportive feedback is replied

Thanx

--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired
functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list
option at
C1 upon selecting March, I want the list in D1 to be reflecting the
options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI

There shouldn't be any blanks in the drop down list.

This prevents any blanks:

Create this named formula (dynamic range for the extracted dates):
InsertNameDefine
Name: List2
Refers to:
=Sheet1!$K$1:INDEX(Sheet1!$K$1:$K$10,COUNT(Sheet1 !$K$1:$K$10))

And, if there are no dates for the selected month, this will prevent the
drop down from working:

Select cell D1
Set up a data validation list.
As the source use this formula:
=IF(COUNT(List2),List2,NA())


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Thanx!

Anyway how to contract the list so as to reflect only the visible values
in
K1:K10? As the blanks, i.e. "" are also shown!

Thanx!

--

Best Regards,
FARAZ A. QURESHI


"T. Valko" wrote:

Forgot something:

Format K1:K10 as DATE


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You'd have to extract all the dates for that month to form another list
then use that extracted list as the source for the drop down list.

With your list of dates in B1:B5 enter this array formula** in K1 and
copy
down to K10:

=IF(ROWS(K$1:K1)<=SUMPRODUCT(--(TEXT(B$1:B$5,"mmmm")=C$1)),INDEX(B$1:B$5,SMALL(IF (TEXT(B$1:B$5,"mmmm")=C$1,ROW(B$1:B$5)-MIN(ROW(B$1:B$5))+1),ROWS(K$1:K1))),"")

Create this named formula (dynamic range for the extracted dates):

InsertNameDefine
Name: List2
Refers to:

=Sheet1!$K$1:INDEX(Sheet1!$K$1:$K$10,COUNT(Sheet1! $K$1:$K$10))

OK

Select cell D1
Set up a data validation list.
As the source use this formula:

=IF(COUNT(List2),List2,NA())

Format cell D1 as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in
message
...
Sure am delighted to hear from you after a long time Max!

I am sorry but I think I wasn't clear enough.

What I meant was that list option of "Data Validation" is required not
auto
filter. I want cell C1 to be reflecting a combo box reflecting Name of
months
Jan to Dec. Upon selecting a month the same kind of Data Validation
combo
box
list in D1 based on a custom formula or some other way to present the
dates
pertaining to such a month only from the source of Column B containing
a
list
of mix dates.

Hopefully a supportive feedback is replied

Thanx

--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One simple alternative ..
Assuming source list in row2 down, dates in B2 down
In C2: =IF(B2="","",TEXT(B2,"mmmyy"))
Copy down, then just apply autofilter on col C for the desired
functionality
to easily filter dates by month/yr.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Suppose I have same sort of list as follows:

CASE1 01-JAN-08
CASE2 15-JAN-08
CASE3 01-FEB-08
CASE4 31-MAR-08
CASE5 15-MAR-08

I have a list of months i.e. January to December presented in list
option at
C1 upon selecting March, I want the list in D1 to be reflecting the
options
of only 31-MAR-08 and 15-MAR-08 i.e. dates falling in March

--

Best Regards,
FARAZ A. QURESHI