If you require users to enter either a dollar amount or a percentage in a recommendation column, but not both, you need to present an error to the user if they try to enter both. You do this by setting up data validation helper columns and data validations that do the following:

  • Add helper columns to your structure that contain formulas that check to see if a value is entered in the dollar amount column and the percentage column.
  • Check for the results of the helper columns in a data validation in both the dollar amount column and the percentage column and present an error alert to the user if they have entered a value in both columns.


For instructions and an example spreadsheet with a data validations setup to allow users to enter a dollar amount or a percentage (not both) following these steps, click here.


  1. Create a Dollar Amount column and a Percentage column on the main tab in your excel structure where users enter a value in either column. In this example, Columns D and E on the Main Tab.
  2. Create a column for a formula that checks for a value in both the Dollar Amount and Percentage columns.  This formula checks for values in both the Dollar Amount column and the Percentage column, and returns a 1 if both columns contain a value. In this example, this formula is in Column H on the Main Tab:  =IF(AND(D3<>"",E3<>"")=TRUE,1,0)
  3. Create a column for a second formula that checks for an error in the first formula. If there is an error, the formula returns a 1 and if no error, it returns the result of the first formula.  In this example, this formula is in Column I on the Main Tab:  =IF(ISERROR(H3),1,H3)
    • Column I results in '1' only if the formula in column H results in '1' (i.e., the formula in column H results in an error).
  1. Create a Data Validation (including an Error Alert message) in the Dollar Amount column and the Percentage column that references Column I.  In this example, the data validation is in Columns D and E on the Main Tab and the data validation formula is:  =AND(I3=0)
    • This data validation formula checks the result of the formulas in the helper columns (in this example, Columns H and I) and if a user has entered a value in both, an Error Alert message will pop up and stop a user from saving this change.


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 in both the Dollar Amount and Percentage columns until they click Save Changes in SecureSheet.