Dependant Lists (Data Validation)

[FARAZ QURESHI]

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

 

[Max]

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.

 

[FARAZ 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

 

[T. Valko]

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):

Insert>Name>Define
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)

 

[T. Valko]

Forgot something:

Format K1:K10 as DATE

 

[Max]

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.

---

 

[FARAZ QURESHI]

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

Forgot something:

Format K1:K10 as DATE

 

[T. Valko]

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):
Insert>Name>Define
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())