If you require users to enter a comment if entering a percentage/number that goes above the max, you need to present an error to the user. You may use a helper column that has a data validation to do the following:

  • Add a column to your structure that contains a formula that checks if one value is greater than another value and if the comment field is left blank.
  • Check the results of the helper column in a data validation and present an error alert to the user if they have entered a value greater than the max and the comment field is still left blank.


For instructions and an example spreadsheet with the data validations setup to require a comment when a percentage is entered outside of range, click here.


  1. Create an input column on the main tab in your excel structure where users enter a value. In this example, Individual Performance Multiplier % (column D).
  2. Create a column for a formula that checks if the comment field is blank when the input value is above the max. If true, the formula returns a 1 and if false, it returns 0.In this example, this formula is in Column G on the MeritBonus tab: =IF(AND(D3>1,(ISBLANK(F3))),1,0)
  3. Create a Data Validation (including an Error Alert message) in the input column (column D in this example) that references Column G. In this example, the data validation formula is:  =AND(G3=0)
  • This data validation formula checks the result of the formula in the helper column (in this example, Column G) and if a user has entered a value above max and has not entered a comment, an Error Alert message will pop up and stop a user from saving this change.
  • For the user to save a value above max, they must enter a comment and then save their changes.


Considerations:

  • Changes must be saved in SecureSheet before the validations will be applied, i.e., the user will not see the Error Alert message if they have entered a value above max and without a comment until they click Save Changes in SecureSheet.
  • After saving a value above max with a comment, a user could clear the comment, save and would not receive the stop alert.

NOTE: Conditional formatting the comments column as red if no comment is entered when a value is above max could be set up as a visual reminder.