In order to display data in a spreadsheet, there are a number of steps required once the Dataset is linked to the spreadsheet.
1. Creating a named range for the Dataset.
Our first step is to create a named range for the Dataset (using Excel). The named range must be named in the following format:
__ [Dataset Name] __ (two underscores followed by the Dataset name and another two underscores)
It must be a single row named range – it will be expanded when the sheet is populated.
2. Displaying Dataset fields in the spreadsheet
For each field in the Dataset you wish to display in the spreadsheet, you enter the Field Name into the cell (inside the ranged range) using the following formula:
##[Dataset Name]##[Field Name]
The field must exist in the Dataset for it to appear. If not, the user will simply see a empty cell.
You can use calculated fields on the values as you would with any Excel formula like =a3+a4.
When the dataset is populated, it expands the name range to include the incoming rows and duplicates the formatting and formula in each cell downwards.
You can insert new columns into the range by inserting a column in the middle of an existing range or by adding them to the end of the range and updating the named range cell references.