Limiting user input

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
[/LIST]
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.

Reply | Quote

Do you have a lot a calculations in the spreadsheet?

Steve

Reply | Quote

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

Reply | Quote

Is the error message that the entry is invalid, that is to be expected.

Steve

Reply | Quote