.
A data marker is a single cell value, but a data marker will usually be populated with several rows of cell values. For example, if you set the data source for the data marker %%=Products.ProductID, to the "ProductID" column in a database table, the marker cell and the cells below it will be populated with ProductID values. New rows will be inserted between the data marker row and the next row in the template that contains a value. This can affect formulas because the range of cell values a formula should reference may change when the workbook is populated.
If, for example, template cell A11 contains the formula =SUM(A9:A9), where A9 contains the marker %%=OrderDetails.Quantity, the range of values that should be added will change after a column of values is inserted at cell A11. In this case, ExcelWriter will stretch the formula so that it references the range of cells from A9 to the last cell that contains an imported value. And the original formula will be pushed down because new rows were inserted between it and the data marker row.
ExcelWriter will move or modify a formula depending on where it is in the template, and whether it references data marker cells:
- If a formula references cells above all data marker rows, ExcelWriter will not move or modify it.
- If a formula references cells below a data marker row, the references will be updated if new rows are inserted between the data marker row and the cells referenced in the formula.
- If a formula references a data marker cell, the formula will be "stretched" to include the imported cell values.
Example
In the ExcelWriter template, cell A11 contains the formula =SUM(A9:A9).
In the populated workbook, the formula is in cell A28 and calculates the sum of values A9 to A26.
