Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Limiting user input

    Hello AllI need to limit the User input to be only the next number higher than what is available.So in column BO I have a CountA formula to add up how many cells it the row are not empty. I can add one to this number and the User can only use an entry that is equal or higher to that number.For example in BO45 has 4. Now if the User puts in BM45 a 3 the system will reject that entry. If the User puts a 1 or a 2 the system will also reject that entry. The User must use 4 or greater to continue.Could I use Conditional format in Excel 2003; or do you think a VBA function should be the way to go?ThanksWassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    I would use data validation on the data tab:

    Allow: Whole number
    Data: greater thean or equal to
    Minimum: =formula

    If you highlight the range of cells first then create the data validation, check "Apply these changes to all other cells with the same settings". If you want to reference the same exact cells in the formula from every cell that you apply the data validation to, then use absolute referencing. ex =$A$1 + $A$2 else, you do not need the $.

    HTH,

    Maud
    Attached Images Attached Images
    Last edited by Maudibe; 2012-10-21 at 13:19.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would use Data - Validation
    Allow: Custom
    Formula:=BM45>=BO45

    You may have to modify if your exacty requirements do not match your example...
    Steve

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Wassim

    I suspect that you do not wish numbers repeated within the range of interest. I suspect that the solutions shown to date will allow this situation if the user enters anything but the next higher number. I offer the following concept.
    • Assume that your input range is $A$10:$L$10
    • In cell A10 enter the following validation formula:
      • =1+MAX($B10:$L10)

    • In cell L10 enter the following validation formula
      • =1+MAX($A10:$K10)

    • In cell B10 enter the following validation formula
      • =1+MAX(MAX($A10:A10),MAX(C10:$L$10))

    • Copy cell B10 and paste it into cells C10:K10

    With this approach, once a number is entered, it can only be deleted or changed to the next acceptable number. Having taken either of these actions, the number that was changed will remain unused in the series unless an Undo (Ctrl + Z), action is invoked as the next step. Having deleted the number from a cell, it can then be populated with the next acceptable number.
    Regards
    Don

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you Maud and SteveBut using data validation seems to produce a delay in processing where the cell value has not yet updated and the validation kicks in and produced an error.I will try and capture screen shots and describe the problem betterThanksWassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Do you have a lot a calculations in the spreadsheet?

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi SteveNot really. I have less than 400. Unless 400 is considered lots.What I see is a value being entered into a validated cell then the validation happens and the error message comes up and I press the ferry button and then I see the control cell get its new value and the validated cell now is in edit mode.This happens on multiple machines so CPU and other speed issues are not in play.Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is the error message that the entry is invalid, that is to be expected.<br><br>

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •