Follow these instructions to use Excel’s data validation features in online spreadsheets and web forms generated by SpreadsheetConverter.
Excel provides a number of built-in data validation features that makes it easier for you to ensure that electronic forms only contain valid information. SpreadsheetConverter supports most of these rules, and will enforce these rules also in the converted web page.
To validate input data in a spreadsheet, Excel allows you define rules for each cell in the Data > Data tools > Data Validation section of the Excel menu. For more information about the Data Validation feature in Excel, please study Excel’s Help pages.
You can assign unique rules for each cell in the spreadsheet. The validation criteria supported by SpreadsheetConverter are:
The Ignore blank checkbox skips the validation rules for empty fields. Tick this checkbox for fields that should be validated only if they contain something.
You can specify what message to display if the field contents are invalid. You can do so from the Error Alert tab of the Data Validation dialog box. Enter your message in the Error Message box. The Title box is not supported.
In the Style dropdown, choose the Stop style if you want to prevent the form from being submitted if the field contents are invalid. The error message will be in a red arrow pointing to the invalid field. Choose the Warning style to have the message appear in a yellow arrow or the Information style for the message to appear in a blue arrow. Always tick the check box Show error alert after invalid data is entered.
The Input Message tab is not supported. Add text near the cell if you need to tell users what kind of data they are expected to provide.
One of the most common and also most complex data validations is to verify that e-mail addresses are correctly written, e.g. that there is only one @ sign, that there is text on both sides of each period etc. The syntax rules for an e-mail address are far too complex to be handled with Excel Data Validation or Excel formulas.
Instead, use the Text > E-mail widget which is a standard text field with built-in validation of e-mail addresses. The field must either be empty or contain a valid e-mail address. If the e-mail address has an incorrect syntax, or if the field is empty but set as Required, the field is considered in error and the form cannot be submitted. This reduces the risk that you receive forms with missing or incorrect e-mail addresses.
The widget only validates the character string in the field; there is no live or online validation that the provided e-mail address actually exists or that someone reads the e-mails sent to it.
If you are validating a date, and you enter the date directly into the Start Date or End Date fields, you must use the system date format specified in the regional settings control panel. If you would prefer to use a different date format, please place the date in a separate cell on the worksheet and give it any date format you like. Then insert a cell reference in the Start Date or End Date fields, i.e. if you’ve placed the date in cell B10 then type =B10 into the Date Validation window. To hide the date so that it doesn’t appear in the converted web page, use the Utility widget or place the date on any hidden row, column or worksheet.
