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.
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.