Since the main purpose of the add-on is to build a data entry form from data stored in a spreadsheet, it is critical that the add-in correctly identifies the data range.
To help describe how the automation implemented in the add-in searches and identifies the data range, let’s use the data in the figure below as an example.
The search for data starts with cell A1 and continues scanning columns and rows. When a cell containing data is found, the search continues until an empty cell in the same row is found. In this example, the first range of cells with data found is in cells A1:E1. The search stopped in cell E1 since cell F1 is empty.
The first range of data found is considered as the Column Header. The rows immediately below this range are considered as the data range, in this example the range A2:E10.
For each cell in the header range, a field on the data entry form is created. Note that 5 fields were created in the form identified with the same text contained in the header cells. In this example the fields created were:
- Id
- Full Name
- Date of birth
- Function
- Note
The data contained in the data region will be displayed within these fields. The add-in also automatically creates a list for better visualization of the data, and can also be used to navigate through the records.
What should be avoided
The main thing that should be avoided is merging cells. In the data range (including column headers) DO NOT MERGE CELLS. Merged cells make it difficult for the add-in to recognize the data range.
Merged cells in header row
Merged cells in the header row make it difficult for the add-in to identify the fields to create in the data entry form.
Merged cells in data rows
Merged cells in the data region can cause different behavior than expected, especially during data deletion.
In the example below, cells C3:C4 are merged. When the user selects one of the records (ID = 2 or ID = 3) and clicks the (Erase) button, the interval A3: E4 will be erased.
Feedback
Post your comment on this topic.