Home Products Support Corporate

Support Knowledge Base, Article 1271

Product

ExcelWriter

Version

3.x and higher

Title

Preserving range reference with ExcelTemplate

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"))