Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 63
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 345
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 345
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sum numbers based on other data Sal Excel Discussion (Misc queries) 6 January 25th 10 09:59 AM
IF Function based on a set of numbers? Scott A Excel Worksheet Functions 11 April 14th 09 04:31 PM
Sum numbers based on specified criteria R.Miller Excel Worksheet Functions 5 June 18th 07 09:50 PM
Adding certain numbers based on... Rich D Excel Discussion (Misc queries) 1 December 9th 06 08:16 AM
How can I rank numbers based on other numbers? NoelMouse Excel Worksheet Functions 1 August 16th 05 12:43 AM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"