Problem
When populating a data marker, ExcelWriter inserts as many rows into the worksheet as there are rows in the data source. If a range reference includes a cell containing a data marker, the range reference will be modified to include all of the newly inserted rows, regardless of whether relative or absolute referencing is used. This behavior is desirable in many situations, such as using ExcelTemplate to populate the source data range for a chart.
However, in some cases, it is desirable that a range reference stays constant, such as when it is used in a formula.
Solution
You can use Excel’s INDIRECT worksheet function to ensure that a range reference is not modified even as new rows are inserted into the range.
For example, you have the following formula: =SUM(A1:A10)
Let’s say one of the cells in the range, A5, contains a data marker. If 10 new rows are inserted into the range when the data marker is populated, the formula will be updated to =SUM(A1:A19)
This behavior occurs even if you use absolute referencing. i.e., the formula =SUM($A$1:$A$10) will be converted to =SUM($A$1:$A$19).
The INDIRECT function converts a text string into a range reference. Because the range reference is created from a text string, it is not modified by Excel when rows are inserted. For the example above, you can preserve the range reference as A1:A10 with the following formula: =SUM(INDIRECT(“A1:A10”))
Related Links
Microsoft KB article for the INDIRECT function
Share the post "Preserving range references with ExcelTemplate"
Follow