Data Validation Help

[dwgnome]

Looking for a way either via data validation or VB code to prevent user entry of a specific codes beyond the given amount. </SPAN></SPAN>

So if cell F42 has a 3 then user should not be able enter more than three vacation carryovers “VC” within the calendar table. The way it is setup. Code “VC” limited to 3 a year as indicated in cell F42. Code “V” for vacation is limited to the number shown in cell F40 and “PH” Personal Holiday is limited to the number shown in cell M40. Is there a way to do this via data validation or VB?</SPAN></SPAN>
</SPAN></SPAN>

Of course a related problem – that I already have each cell’s data validation used for preventing the user to enter H for holiday in cells that fall on weekends using:</SPAN></SPAN>

=NOT(OR(AND(WEEKDAY(DATE($C$3,COLUMN(E:E),$B32),2)>5, LEN(G32)), AND(COUNTIF($S$5:$S$12,DATE($C$3,COLUMN(E:E),$B32)), UPPER(G32)<>"H")))</SPAN></SPAN>

Where the list of holiday dates are provided in range S5:S12.</SPAN></SPAN>

In experimenting with just the VC portion, I entered in data validation and under custom I entered the following formula: </SPAN></SPAN>

=COUNTIF(C5:F35,"VC")>3</SPAN></SPAN>

However, it did not seem to work when tested as true as it still allowed the letters VC to be entered despite that there were 4 other “VC”s entered in the columns C through E:</SPAN></SPAN>

Any help would be appreciated. Using Excel 2003, native functions only as I cannot use Morefunction or other add-ins due to workstation limits.
</SPAN></SPAN>

 

[Andrew Poulsom]

Shouldn't it be?

=COUNTIF(C5:F35,"VC")<=3

 

[dwgnome]

Thanks Andrew, I had it reversed . Also to include my other conditions the following seems to work:

=AND(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)

Now how can I incorporate back the displaced formula:

NOT(OR(AND(WEEKDAY(DATE($C$3,COLUMN(E:E),$B32),2)>5, LEN(G32)), AND(COUNTIF($S$5:$S$12,DATE($C$3,COLUMN(E:E),$B32)), UPPER(G32)<>"H")))</SPAN></SPAN>

As there is only one data validation per cell. I there a way to do this using visual basic code and can someone help with that?

 

[Andrew Poulsom]

Why do you need to use VBA?

 

[dwgnome]

I suggested VBA as the data validation for each cell in the table is now used for the user input restriction.

Where else can the formula I had to prevent the user from entering an "H" for holiday in cells that fall on weekends?

 

[Andrew Poulsom]

Do you want to combine those formulas?

=AND(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,NOT(OR(AND(WEEKDAY(DATE($C$3,COLUMN(E:E),$B32),2)>5, LEN(G32)), AND(COUNTIF($S$5:$S$12,DATE($C$3,COLUMN(E:E),$B32)), UPPER(G32)<>"H"))))

 

[dwgnome]

Your suggestion worked well. Thank you.

Did not think you can have two completely different sets of instructions in one combined validation. Only thing, I have to double up on the error alert.

Cannot Exceed the number of VC, V or P/H allowed.
No input allowed on weekends!

 

[Andrew Poulsom]

I'm afraid that's not possible. Can't you combine the error messages, telling the user that it's either one or the other?

 

[dwgnome]

Yes, essentially that is what the two lines listed in my last post is doing.

Again, many thanks for your assistance.