How do I limit the number of times a selection can be made from a drop-down list

[Jonnylogan]

Hi guys!

So, I'm creating a diary so that I can forward date jobs that I need to do, I want to limit the dates I select by using data validation which references a list of dates when I can complete the tasks (i.e a list that only contains week-days and days when I'm not on holiday).

I also want that drop down list to limit the number of entries I can make for a certain date, so that, for example, a specific date can only be chosen from the list, a maximum of 3 times.

I've been able to get each of these to work on their own, but I can't seem to combine them. The validation I'm using is as follows;

=$AB$311:$AB$2659 - to limit the entries to the dates in a list

=COUNTIF($F$19:$F$474,F1)<=3 to limit the number of times that a date can be selected in the diary

but I need to get both of these functions to work together.

Any ideas?

Cheers guys,

Jon

 

[Luke M]

Do you need to keep the dropdown, or are you ok with typing in the dates yourself? If so, could do a custom validation of:
=AND(ISNUMBER(MATCH(F1,$AB$311:$AB$2659 ,0)),COUNTIF($F$19:$F$474,F1)<=3)
Where F1 is the cell you're applying the validation to.

Alternatively, we can change the source. Since your data is in AB311:AB2659, you could use column AC with a formula like:
=IF(COUNTIF(F$19:F$474,F1)<=3,AB311,"")
ends up creating a list with lots of blanks, but we can work around that if needed.

 

[Somendra Misra]

Hi Jon,

See the attached sample, not exactly a dual data validation but a combination of data validation and macro message box.

Regards,

 

[Jonnylogan]

Hi guys!

I'd really like to keep the drop down option, if possible. On the train now so can't test your solution Luke, am looking forward to getting back into the office and giving it a blast!

 

[Luke M]

Further reading, I got the idea from Debra's article here:
http://www.contextures.com/xlDataVal03.html

Need to adjust it somewhat to match your criteria, but it's not too different.

 

[Jonnylogan]

Luke,

I've managed to get the solution using Debra's article to work. Think i need some modification though.

The current solution uses:

=IF(COUNTIF('ACTIVE CASES'!$E$17:$E$400,A2)>=4,"",ROW())

and then

=IF(ROW(A1:A2349)-ROW(A1)+1>COUNT(B1:B2349),"",
INDEX(A:A,SMALL(B1:B2349,ROW(INDIRECT("1:"&ROWS(A1:A2349))))))

with validation on cells taking source from Dates which is defined as =OFFSET(Dates!$C$1,0,0,COUNTA(Dates!$C$1:$C$2349)-COUNTBLANK(Dates!$C$1:$C$2349),1)

What I want, now, is that the dates which are available in my diary (i.e when I click my drop down) are only the dates in the future.

Any ideas? I've tried wrestling with the dates in A1:A2349 using the date() function, with variations on Date()+1 etc, but I lose some data.

The dates in A1:A2349 have been formatted to display weekdays only and I have then manually deleted out the days/weeks which remain, representing the dates that I am not available to complete those taks.

I've tried Vlookups to cross reference the dates in A1:A2349 with the present day and future dates, but can't get it to work.

Any ideas?

 

[Luke M]

Change this formula:
=IF(COUNTIF('ACTIVE CASES'!$E$17:$E$400,A2)>=4,"",ROW())
to this:
=IF(OR(A2<=TODAY(),COUNTIF('ACTIVE CASES'!$E$17:$E$400,A2)>=4),"",ROW())

That will let us check both conditions at the same time.