Data Validation Problem

[Barking_Mad]

Hi there,

I have two columns as below

A | 1
B | 2
C | 3
| SUM

A to C holds names and 1,2,3 are validated numbers and cannot individually exceed 14.

SUM at the bottom adds the totals of 1,2,3 etc.. I have set it up so that it validates to make sure the total does not exceed 45.

However when I enter the values in 1,2,3 it allows it to sum them above 45 with no error. Any ideas where im going wrong?

Thanks

[DonkeyOte]

I don't understand how they can > 45 if they are restricted to <= 14... 3 x 14 = 42.

Post a sample file with your validation in place.

[kroz]

Can you tell us how you've set up the validation of the SUM so that we can figure out where the error is?

[kroz]

I don't understand how they can > 45 if they are restricted to <= 14... 3 x 14 = 42.

Post a sample file with your validation in place.

SUM at the bottom adds the totals of 1,2,3 etc..

I think 1,2,3 is generic only

[DonkeyOte]

Regardless the issue is not with the Validation of the SUM value, the Validation issue resides in the individual cells that comprise the SUM... you can't use the SUM to restrict other cells... you must calculate the SUM within the Validation of the component parts at time of execution.

[kroz]

maybe he does something like this:

Please Login or Register  to view this content.

[DonkeyOte]

No I don't think so... Validation does not write back a result it merely restricts actions.

I suspect the Validation formulae that should be used in B1:B10 would in fact be along the lines of:

=AND(B1<=14,SUM($B$1:$B$10)<=45)

At which point only numeric values <= 14 can be entered in the range and only where the resulting summation <= 45 else Error Dialog is displayed and action can not be confirmed... to test highlight B1:B10 and insert the above Custom Validation rule.

There should be no need for any Validation in the Summation cell (say B11).

[Barking_Mad]

Ok, here you go. It's probably me being a fool as im v poor with excel....

The validation is in D29 (bowling) and should not allow D5:D29 to come to more than 45, with those individual cells not allowing more than an input of 14 (which it does not).

Im thinking it's an issue with the SUM and validation.....?

[DonkeyOte]

Not long now to the start of the season... hooray!

As outlined you don't want/need validation in the Summation cell... the key is for the Overs to be restricted such that a Max of 14 can be assigned but in addition you can't have > 45 in total...

Step 1:

Highlight D29 and remove the Validation altogether.

Step 2:

Highlight D5:D28 (select D5 first) and then alter the Validation rule such that it is in fact set to Custom:

Formula: =AND(D5>0,D5<=14,SUM(D$5:D$28)<=45)

Change the Error Alert Narrative:

Maximum overs for each bowler is 14 and for Match 45


You will now find you can not enter more than 14 overs for a bowler and than when entering Overs you can not enter a value that would result in the Team's overs exceeding 45 in total.

The Validation rule is setup such that it can be copied to columns L, T etc without need for alteration.

Presumably we need not worry about partial overs... ie if incomplete implication being that fixture ended without 45 overs being bowled.

[DonkeyOte]

Here, on reflection - to assist - I've made some other changes to your Validation rules... I hope it helps.

Summary of changes in addition to the Overs validation:

Maidens Rules:
Must be >= 0
Must be Whole Number
Can not Exceed Overs Bowled

Runs Rules:
Where Maidens < Overs Bowled Must be >= 0 and <= 200
Where Maidens = Overs Bowled Must be 0
Must be Whole Number

Wickets Rules:
Overs Bowled Must Exceed 0
Must be >= 0 and <= 10 & Team Wickets can not Exceed 10
Must be Whole Number

4's
Overs Bowled Must Exceed 0
Must be >= 0 and <= x where x is determined by Runs Scored...
ie Total of 4's + 6's can not exceed Runs Conceded
Must be Whole Number

6's
Rules as above

The above validation rules have been applied to each fixture table.

EDIT: Upload removed & reposted at 10:04 UK Time

[Barking_Mad]

Oh a cricket playing donkey! Marvellous - yes not long to go now! Thanks very much for all your assistance!

Can I be really cheeky? I have another question, I actually think you answered it a while ago, but the design has changed since meaning your solution doesn't work anymore.......

I need to find the best bowling figures for each bowler and place the result in the ‘Best Bowling’ column in the BOWLING STATS tab.

This means reading the adjacent WICKETS & RUNS columns in the ‘Bowling vs.’ tab for each game (with each game being in non-adjacent cells) and obviously find the highest number of wickets for the lowest number of runs……

I shall be forever in your debt if a solution can be given!

[DonkeyOte]

Attached is a revised upload re: Validation changes as per my penultimate post.

I will look at your latest question and post back subsequently.

[DonkeyOte]

What is the password for your Stats sheet ?

[Barking_Mad]

Hi, I just PM'd you, answer at the bottom!

[DonkeyOte]

Attached is an updated version...

A few changes ... for ex. on Bowling Page it makes sense to store Extras & Other Dismissals (eg Run Outs) ... then set the Results Page to feed directly from the Bowling Page.

The Bowling Stats page has been updated... I've replaced some of your formulae -- eg Overs bowled etc... also try to limit the amount of Conditional Formats you run as these are Volatile and will slow the file down.

As an alternative to your Conditional Formats to handle Errors use IFs as outlined to validate as to whether or not a given calculation should be performed in the first instance and where not the case return a blank to the cell thereby dispensing with Conditional Format requirement.

Re: B-B ... as you know this is a non-trivial exercise... I've added 2 helper columns to the Bowling Stats page which can be hidden, these are used to populate the B-B string.

I hope that helps.

[Barking_Mad]

Cheers for all that, much appreciated! I should learn excel properly!