Configuring forms for automatic completion

Configuring Text Fields

Text fields are eligible for automatic completion by Excel upload. When editing these fields in the form builder, you can find a section in the configuration called “Spreadsheet reference”. This entry field accepts Excel formulas. Most Excel formulas for looking up data are supported. These include, among others, V and H -lookup, index/match, and direct cell references. It is advised to construct more complex formulas in the Excel file first, before entering the formula in the field configuration.

When working with Excel files with multiple sheets, it is also advised to use the sheet reference in the formula. A cell reference formula with a sheet reference is built up in the following structure:

=<sheetname>!<cellreference>

For example:

=Sheet2!B23

Not using a sheet reference in the formula will automatically point to the active sheet, saved in the Excel file.

Testing reference mapping

Once a form contains all of necessary mappings, it can be validated by matching with an example Excel. This can be done choosing “Test reference mapping” in the quick actions menu.

An Excel can now be uploaded. Once uploaded, a section at the top of the form appears confirming the selected Excel file. If correct, “Validate” can be clicked to perform the test. As long as the current session in the form builder is active, the reference file will remain. This allows you to make quick and easy adjustments to the spreadsheet references and re-test, without having to upload new files every time.

Once validated, each configured field in the form will show the value found in the uploaded Excel. There are three possible outcomes:

• The references correctly found are shown in green.

• The references that didn’t return any value are shown in yellow. This is mainly due to the found cell in the Excel file being empty

• The references that return an error are shown in red. Here, the Excel error will be shown. For further help with understanding these Excel errors, please check https://support.microsoft.com/en-us/office/detect-errors-in-formulas-3a8acca5-1d61-4702-80e0-99a36a2822c1

For a comprehensive look at automatic form completion by Excel, please see How-to: Automatically Fill in Form