Defining a Dynamic Named Range

Because the data is inserted in the template when the report is created, it is impossible to create charts in the template with static range as chart source data. In order to create charts, Excel tables, and pivot tables on data inserted at report creation time, you need to create a Dynamic Named Range.

You can reuse the report created in the Your First Excel Report. You can see that the first row of the data starts in cell A3 if you include the headers.

You can then create a Dynamic Named Range that includes all the data from this list, including the headers, even when rows are added or removed from the list.

The trick is to use the Excel functions OFFSET (to select a dynamic range) and COUNTIF (to count the number of rows with data). If you start at cell A3 and have a table with seven columns, the corresponding formula looks like the following:

=OFFSET('${TITLE}'!$A$3,0,0, COUNTA('${TITLE}'!$A$3:$A$999999), 7)

This formula basically states:

Select a range starting at Cell A3, with a width of 7 columns and a height equal to the number of cells with a value between A3 and A999999.

This means:

  • You should not have any cells with data in the column A after this list.
  • You should not have any cell without data in the list for column A. Here, column A is the resource full name, so it is not supposed to be empty; but you cannot pick the Role column for computing the dynamic range because some of the Role values are empty.

Note that the spreadsheet name with the placeholder in the formula (${TITLE}) is included; it is automatically updated when the spreadsheet is renamed during report generation.

If the number of columns is also dynamic, you can replace the columns number by another COUNTIF formula:

=OFFSET('${TITLE}'!$A$3,0,0, COUNTA('${TITLE}'!$A$3:$A$999999), COUNTA('${TITLE}'!$A$3:$ZZ$3))

You can then create a Named Range DataRange with the previous formula. You can create it in menu Formulas/Name Manager, then click New and create it with a scope of Workbook so that it can be accessed from any spreadsheet. Input DataRange for the Name, and the previous formula for the Refers to: field.

If you click the Excel Name Box from the input field in the top left corner under the ribbon where the active cell number is displayed, and input DataRange , it should select the whole list of data in your spreadsheet.