Workbook Features

• Excel

  For custom formatting of cells, right click and select the Format Cells… In this dialog decimal places, date formats, currency, percentage, and other options can be specified. On the Number tab, the Custom Category provides more advanced formatting e.g., mm/dd/yy h:mm:ss gives the date and time to the second.

• XLReporter Template Studio

  For custom formatting of cells, right click and select the Format Cells… With the Format button decimal places, date formats, currency, percentage, and other options can be specified. The Custom option provides more advanced formatting e.g., mm/dd/yy h:mm:ss gives the date and time to the second.

• Excel

  These settings are available in the Format Cells dialog under the Border and Fill tabs.

• XLReporter Template Studio

  These settings are available in the Format Cells option under the Border and Font sections.

• Any Value

  Any input is accepted for this type. This is applicable for fields in a form that you do not need any validation for, however still want to utilize the custom input prompt for providing instructions.

• Whole Number

  The field can accept a whole number that meets the specific criteria.

  For example, a field can only accept an integer between a min and max value contained in cells C10 and C11, respectively.

  • Allow: Whole Number

  • Data: Between

  • Minimum: =C10

  • Maximum: =C11

• Decimal

  The field can accept a decimal number that meets the specific criteria.

  For example, a field can only accept a decimal number that is less than or equal to 5.

  • Allow: Decimal

  • Data: less than or equal to

  • Maximum: 5

• List

  A drop-down list is provided to select from. It is recommended to use this option whenever possible to prevent against manual entry errors and to keep the database entries consistent. In addition, dynamic lists can be created by using formulas.

  With this option, In-cell dropdown should always be checked.

  For example, the field requires one operator name to be entered. There are 10 total operator names that could be chosen. Their names are listed in cells J1 to J10.

  • Allow: List

  • Data: N/A

  • Source: =$J$1:$J$10

• Date

  The field requires a date to be entered that meets the conditions set.

  For example, the field can accept a date that is within today’s date and a week from today.

  • Allow: Date

  • Data: Between

  • Start date: =TODAY()

  • End date: =TODAY() + 7

• Time

  The field requires a time to be entered that meets the condition set.

  For example, a time must be entered between normal working hours 9am to 5pm.

  • Allow: Time

  • Data: Between

  • Start time: 9:00 AM

  • End time: 5:00 PM

• Text Length

  The field requires text to be entered that meets the condition set.

  For example, the field is a comment field that requires text but is limited to 255 characters so it can be inserted into a Text data field.

  • Allow: Text Length

  • Data: Less than or equal to

  • Maximum: 255

• Custom

  The field uses a calculation/formula to determine if data entered is valid. The formula must evaluate to TRUE for valid entries and FALSE for invalid entries. This is the most dynamic of the validation types because it can use a vast array of formulas. The value of other cells can be used to determine validation as well.

  For example, the field configured for cell B10 can only accept text. Use =ISTEXT(cell) as the formula to validate that only text is entered.

  • Allow: Custom

  • Formula: =ISTEXT(B10)

  In another example, the cell A11 can only accept a product ID starting with “PROD” and ending with a number. E.g., PROD459789.

  • Allow: Custom

  • Formula: =IF(LEFT(A11,4)=”PROD”, TRUE, FALSE)

Most validation types offer the Ignore blank option which is found in the Settings tab. To ensure the user must enter a value, uncheck this option. When the form is deployed, the cell will appear with a red background when the form is deployed until data is entered.

If you wish to create a required cell with no additional validation, set Allow to Any Value. However, the Ignore blank option is disabled. To work around this problem:

• Switch Allow: to Whole Number.

• Uncheck Ignore blank.

• Set Allow: back to Any Value.

• Input Message

  This option, when enabled, displays a prompt when the user selects the cell to edit. It can be used to give instruction on what type of data is required for this field.

• Error Alert

  This option (when enabled) prompts the user after invalid data is entered in the cell to alert them that it is invalid. In Data Entry Forms only the Stop Style is supported, the others should not be used. When invalid data is entered, a Stop alert message is shown. There are 2 options available:

  • Retry to enter a new value

  • Cancel to remove the current value from the cell

  If this option is not enabled and the user enters invalid data, the data remains in the cell. This can be useful for cells that present a list of values to select from but also allow for the user to enter something manually. For example, on a form a user must fill out if a piece of equipment fails, a list of common failure reasons may be provided, but there may also be the need for a user to enter a custom reason that is not on the list.

• Excel

  Named cells/ranges are defined in Excel a few different ways.

  • Under the Formulas tab, in the Defined Names section, click Define Name.

  • Under the Formulas tab, in the Defined Names section, click Name Manager. In Name Manager click New.

  • With the cell/range to name highlighted in the worksheet, click into the Name Box to the left of the Excel’s formula bar, enter a Name (replacing the cell reference shown) and press the Enter key.

• XLReporter Template Studio

  • Under the Design tab, select Content, Insert, Named Range.

  • Under the Design tab, select Data, Name Manager.