Data Validation To Limit Code Entries

[dwgnome]

I am trying to come up with a way to limit the number of codes ("V", "VC" and "P/H") that can be entered on the following vacation calendar. This is to prevent from going over the allocations provided below.

Vacation

*	B	C	D	E	F	G
1	Vacation Schedule	*	*	*	*	*
2	*	*	*	*	*	*
3	Year:	2012	*	*	*	*
4	Month/Day	Jan	Feb	Mar	Apr	May
5	1	*	*	*	*	*
6	2	H	*	*	*	*
7	3	*	*	*	*	*
8	4	*	*	*	*	*
9	5	*	*	*	*	*
10	6	*	*	*	*	*
11	7	*	*	*	*	*
12	8	*	*	*	*	*
13	9	*	*	*	*	*
14	10	*	*	*	*	*
15	11	*	*	*	*	*
16	12	*	*	*	*	*
17	13	*	*	*	*	*
18	14	*	*	*	*	*
19	15	*	*	*	*	*
20	16	*	*	*	*	*

<tbody>
</tbody>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 C5 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * D5 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * E5 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * C6 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * D6 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * E6 Custom * =OR(COUNTIF($C$5:$N$35,"VC")<=$F$42,COUNTIF($C$5:$N$35,"V")<=$F$40,COUNTIF($C$5:$N$35,"P/H")<=$M$40) * <tbody> </tbody>

<tbody>
</tbody>

Vacation

*

B

C

D

E

F

G

H

I

J

K

L

M

40

Total Vacation Days Provided at Start:

*

*

*

20

*

Total Personal Holidays Provided at Start:

*

*

*

*

1

41

*

*

*

*

*

*

*

*

*

*

*

*

42

2011 Vacation Days Carryover:

*

*

*

3

*

Total Days Vac at Beginning of 2012

*

*

*

*

24

43

*

*

*

*

*

*

*

*

*

*

*

*

44

*

*

*

*

*

*

*

*

*

*

*

*

45

Total Days Remaining YTD:

*

*

1

P/H

23

V

*

*

*

*

*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B42 =YEAR(U5)-1&" Vacation Days Carryover:" F42 =' Vacation 11'!I45 H42 ="Total Days Vac at Beginning of "&YEAR(U5) M42 =SUMIF(F40:M40,">"&0)+SUMIF(F42,">"&0) E45 =SUMIF(M40,">"&0)-IF(COUNTIF(C5:N35,"=P/H")>M40,M40,COUNTIF(C5:N35,"=P/H")) G45 =SUMIF(F40:F42,">"&0)-((COUNTIF(C5:N35,"=V")+(COUNTIF(C5:N35,"=VC")))) I45 =IF(AND(G45>0,TODAY()>N4+28),G45,"") J45 =IF(AND(G45>0,TODAY()>N4+28),"Carryover to "&C3+1,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

So for example, if the limit of Vacation carryover is 3, then I should only be allowed to enter three "VC" entries anywhere in the entire table. The same should be true for Vacation "V" days and personal days "P/H" for their respective allotments.

The DV I came up with still allows more than the allotment until all three are exceeded.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

A little help please!

 

[dwgnome]

Could use someone's expertise please.....