Only allow certain numbers based on another value

[duketter]

Excel 2007 - I have a worksheet that has a column that shows the number of
available units left based on the sum of units at all locations. So, there
is only 6 available at any given week. I then sum up columns D:K and then
input how many are available from the 6. So if column D shows 2 and none of
the other columns show usage then there are 4 left for that week. My
problem/issue comes when I want to only allow the individual entering a
number for that week to only be able to enter up to what is available that
week. So in the example, the next user could only enter 4 since that is all
that is left. If they enter a 6, I would like an error message or something
to come up saying only 4 are left to choose from. Here is my formula in the
available units cell: =$O$10-SUM(D6:K6) $O$10 contains the number 6 for
total available units, that never changes. D6:K6 show how many each
individual wants to use.

 

[Ron@Buy]

Use 'Data Validation'
Validation Criteria > Whole number
Data > Between
Minimum: 1
Maximum: 6
You can use 'Input message' to advise user of criteria and 'Error Alert' to
'stop' if numbers do not compute to 6.
Hope this helps.

 

[Ron@Buy]

Sorry re-reading your question again the Validation Criteria should be Custom
Then in formula enter =Sum(D6:K6)=6
Enter Input Message and Error Alert to suit.