1. ## 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

2. 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

3. 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. 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.

5. 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

6. Do you have a lot a calculations in the spreadsheet?

Steve

7. 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

8. 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
•