Sparse
The Sparse() function, together with Excel’s implicit intersection operator (=@), allows the user to edit blank cells inside a returned result.
When the function recalculates, any manually entered values are not overwritten. This applies to both Excel and Quintessence functions.
Syntax:
Qx.Sparse(Range)- In Excel with Quintessence add-in versions prior to 25.1, use:
Sparse()
ⓘ See parameter descriptions
| Range | The range of data used as input. |
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Allowing user input inside a recalculating form
A form is provided via a Report, called User Form, in range A1:C3:
| Name: | Surname: | |
| Complete form | ||
| Approval status: | FALSE |
The user will enter Name and Surname in the row labeled Complete form.
The Approval status updates automatically when the form is completed. Therefore, the user’s input must remain unchanged even when the report refreshes.
This can be achieved by combining Sparse() with implicit intersection.
Instead of using:
=Qx.Report("User Form")Use:
=@Qx.Sparse(Qx.Report("User Form"))This allows the user to freely enter their details, and when the report recalculates, their values will not be overwritten.
Result:
| Name: | Surname: | |
| Complete form | John | Doe |
| Approval status: | TRUE |