Advanced Validation Techniques

Advanced Validation Techniques in Designer Applications

  1. Setup a Dynamic Input Range Based on Category Selection:

    • Create a dropdown menu to select a category (e.g., Category A, B, or C).
    • Use a lookup formula to dynamically fill two cells with the minimum and maximum values for the selected category.
    • Apply data validation to another cell:
      • Choose “Whole number.”
      • Set “Between” as the data type.
      • Reference the cells with the minimum and maximum values.
    • Example:
      • Selecting Category A limits the input range to 1-10.
      • Entering a number outside this range (e.g., 0) triggers an error, while a valid number (e.g., 5) is accepted.

  2. Implement Validation in SpreadsheetWeb Designer:

    • Upload your Excel file to SpreadsheetWeb.
    • Ensure the “Use Excel Data Validation” option is selected.
    • Test the validation:
      • Choose a category and enter a value. The designer application will enforce the same rules as in Excel.

  3. Date Validation Example:

    • Set up two date input cells: one for the start date and another for the end date.
    • Create a validation rule ensuring the end date is at least 30 days after the start date.
    • Example:
      • Selecting a start date of 08-01-2024 and an end date of 08-15-2024 triggers an error.
      • An end date 30 days beyond the start date is accepted.

  4. Implement Date Validation in SpreadsheetWeb Designer:

    • Upload the Excel file and test the date validation in the designer application.
    • The system should enforce the 30-day rule just as it does in Excel.

  5. Dependent Dropdowns Example:

    • Create two dropdown menus.
      • The first dropdown contains options (e.g., List 1, List 2, List 3).
      • The second dropdown should change its options based on the first dropdown selection.
    • Use named ranges and the INDIRECT formula to link the dropdowns.
    • Example:
      • Selecting List 1 in the first dropdown should populate the second dropdown with options related to List 1.

  6. Implement Dependent Dropdowns in SpreadsheetWeb Designer:

    • Upload the Excel file.
    • Test the dependent dropdowns in the designer application to ensure they function as intended.