Range Types

You can define four different kinds of bands as follows:

• __ : This range moves down and inserts full rows. For example: __CUSTOMER__

• _ : This range is similar to __ but cells outside of the range won't move down. For example: _CUSTOMER_

• II_ : This range moves to the right and inserts full columns. Note that the first character is the letter i, not a pipe (|). For example: II_CUSTOMER_II

• I_ : This range is similar to II_ but cells outside of the range won't move right. For example: I_CUSTOMER_I

In the following example, if you name A1:D7 as _CUSTOMER_ Cell E8 won't move when inserting down. If you name it as __CUSTOMER__ Cell E8 will move to the last inserted cell.

X Ranges

This is a special range where X means delete, which would remove the last row after a named range inserts the new rows.

Let's imagine we want to make a simple report on a list of items and their prices.

So, we create a new template, define a title row, and insert a named range on A2:B2 to propagate the data. But we also want to know all the total cost of all items, so we add a formula on B3:

When you run this report, rows will be inserted between row 2 and 3, but the formula SUM(B2:B2) won't change. Nothing has been inserted between B2 and B2, so the sum range will remain constant.

So, we need to have a SUM range that can expand. We will define:

Now, when rows are inserted between row 2 and 3, the formula will be updated to reflect the new rows.

In this particular case, this solution might be enough. Just leave an empty row after the range so formulas expand, and then you can hide the extra row or just leave it there.

But, if we were for example creating a chart, this extra row will be on the chart too.

This is where the X range comes to rescue. X ranges are normal named ranges with an “X” at the end. Once it is expanded it will erase the row immediately after the range, or column if it is a column range.

Example of X ranges: __Customer__X, |_Customer_IX