set a limit on value of a cell

[michaelmullen]

I need to set a limit on the value of the cell that adds up cells B14:B23 so if the value of the cell is equal to or greater than 16 a error message will appear telling the person the value of the cell is to high.

[NBVC]

=if(Sum(B14:B23)>16,"Value to High","")

[davesexcel]

Here's a good description on data validation
http://support.microsoft.com/kb/211485

[michaelmullen]

that formula works but i need a error message like when you break a data validation

[NBVC]

So you will be entering a number physically in a cell and you don't want to allow it if the sum of a range is more that 16?

Data Validation

Allow: Custom

=Sum(B14:B23)<=16

Then go to Error message tab and enter the error...

[daddylonglegs]

You won't be able to put the validation on the cell with the sum, try using validation on the range B14:B23

Select that range and then use this custom formula

=SUM(B$14:B$23)<16

Note the $ signs

[michaelmullen]

i simplified it down in my original post. i have uploaded the document i am trying to limit. on sheet 1 there in a picture with different health and safty problems on. each time they click a correct problem they are award a point. but the problem is they can click the picture unlimited times. i need a way to limit the amount of clicks to 15. i have set up macros to count the amount of times they clcik the picture but i need to limit it to 15. on sheet 1 is the picture. sheet 2 is used for calulations on sheet 2 cell B9 is the amount they have clicked the picure i am trying to limit that number so it can not exceed 15. sheet 3 is the summary sheet

[michaelmullen]

i simplified it down in my original post. i have uploaded the document i am trying to limit. on sheet 1 there in a picture with different health and safty problems on. each time they click a correct problem they are award a point. but the problem is they can click the picture unlimited times. i need a way to limit the amount of clicks to 15. i have set up macros to count the amount of times they clcik the picture but i need to limit it to 15. on sheet 1 is the picture. sheet 2 is used for calulations on sheet 2 cell B9 is the amiunt they have clciked the picure i am trying to limit that number so it can not exceed 15.