Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only allow certain numbers based on another value
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only allow certain numbers based on another value
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. "duketter" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only allow certain numbers based on another value
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. "Ron@Buy" wrote: 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. "duketter" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum numbers based on other data | Excel Discussion (Misc queries) | |||
IF Function based on a set of numbers? | Excel Worksheet Functions | |||
Sum numbers based on specified criteria | Excel Worksheet Functions | |||
Adding certain numbers based on... | Excel Discussion (Misc queries) | |||
How can I rank numbers based on other numbers? | Excel Worksheet Functions |