Data validation with sum of values limit

[estephenkim]

I'm creating a user input sheet of weights from a data validation list for each of four items (see image below). I'd like to code it so that it prevents the user from being able to enter (or at least issues a warning against entering) a cell value that results in the sum total of all four cells exceeding 100%. Is this possible to do? If so, how would you do it?

 

[JoeMo]

You can set the validation condition for each of the 4 cells separately. For example, for A3 the custom validation condition is:

=A3<=100-SUM(A1,A2,A4)

Use analogous conditions for each of the other 3 cells. Remember that the user can defeat validation using a paste to any or all of the 4 cells. If you want to protect against pasting, you will need VBA.

 

[estephenkim]

Thanks for your response, JoeMo. The custom validation formula addressed the total weight limit and provided a nice, instantaneous error-correction situation, but I wanted to keep the drop-down list functionality and I still had the issue with the copy & paste loophole. Also, I hadn't considered the situations in which the total weight summed to less than 100%. Thus, I decided to add a user instruction message and a Total weight (%) cell, to which I added a couple conditional formats to change the cell color green or red, to help the user ensure that the total weight equals 100%.