Data Validation To Limit Code Entries

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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
Vacation Schedule
*
*
*
*
*
*
*
*
*
*
*
Year:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: center"]2012
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="bgcolor: #FFCC00, align: center"]Month/Day
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]Jan
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]Feb
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]Mar
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]Apr
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]May
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]2
[/TD]
[TD="bgcolor: #FF99CC, align: center"]H
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: center"]3
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="align: center"]4
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="align: center"]5
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="align: center"]6
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]
[TD="align: center"]7
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]
[TD="align: center"]8
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]
[TD="align: center"]9
[/TD]

[TD="bgcolor: #CACACA, align: center"]14
[/TD]
[TD="align: center"]10
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]15
[/TD]
[TD="align: center"]11
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]16
[/TD]
[TD="align: center"]12
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]17
[/TD]
[TD="align: center"]13
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]18
[/TD]
[TD="align: center"]14
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]19
[/TD]
[TD="align: center"]15
[/TD]
[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #FFFF00"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]20
[/TD]
[TD="align: center"]16
[/TD]

</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
Total Vacation Days Provided at Start:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
2011 Vacation Days Carryover:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Total Days Remaining YTD:
*
*
*
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]40
[/TD]

[TD="align: center"]20
[/TD]

[TD="align: left"]Total Personal Holidays Provided at Start:
[/TD]

[TD="align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]41
[/TD]

[TD="bgcolor: #CACACA, align: center"]42
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: left"]Total Days Vac at Beginning of 2012
[/TD]

[TD="align: center"]24
[/TD]

[TD="bgcolor: #CACACA, align: center"]43
[/TD]

[TD="bgcolor: #CACACA, align: center"]44
[/TD]

[TD="bgcolor: #CACACA, align: center"]45
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: left"]P/H
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: left"]V
[/TD]

</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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
K
  • Question Question
conditional formatting question
Replies
2
Views
564
kerm007
K
V
  • Question Question
Rank function
2
Replies
12
Views
362
Fluff
Fluff
T
  • Question Question
Dynamic Sheet Queries
Replies
1
Views
266
Vogateer
V
S
  • Solved
Need data where ever we get all the letters
Replies
3
Views
144
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top