Dynamic Range Calculated Columns

Modified on Sun, 26 Jul at 4:17 AM

Overview

A QQube Dynamic List is one connected block of data that can expand, contract, or reorder when the source is refreshed.

For that reason, calculations should not be created outside the Dynamic List by referring to fixed worksheet cells.

Avoid Untethered Cell References

A formula that refers to a specific worksheet cell may return the wrong result after a refresh because the source value can move to a different row.

Instead, add a calculated column directly to the Dynamic List. The new column becomes part of the table and expands or contracts with the QQube data.

When to Use a Calculated Column

Create Row-Level Calculations

Use a calculated column when the formula must evaluate the value in each individual row, including IF statements that cannot be created with a standard PivotTable Calculated Field.

Convert Text Values to Numbers

Use a calculated column to convert Custom Field values stored as text into numeric values that can be used in formulas or PivotTables. Review the Support Guide on Custom Fields for additional information.

Create a Calculated Column

1. Insert a Column Inside the Dynamic List

Right-click any cell in the last column of the Dynamic List.

Select Insert > Table Columns to the Right.

Excel menu showing Table Columns to the Right for a QQube Dynamic List

2. Rename the New Column

Excel adds a new column that becomes part of the Dynamic List.

Replace the default column heading with a meaningful name.

New calculated column added to a QQube Dynamic List

3. Enter the Formula

Enter the Excel formula in the first data row of the new column.

Excel automatically fills the formula through the entire Dynamic List and applies it to new rows after a refresh.

Excel calculated column formula repeated through a QQube Dynamic List

Example Formula

The following formula returns the Sales Amount when the Item Name is Cover -DB. Otherwise, it returns 0.

=IF([@[Item Name]] = "Cover -DB",[@[Sales Line Sales Amount]],0)

Why Structured References Matter

The formula refers to field names within the current row instead of fixed worksheet cells. This keeps the calculation aligned with the correct QQube record after sorting, filtering, or refreshing.

Expected Result

The calculated column remains part of the Dynamic List, fills the formula through every row, and continues to reference the correct QQube fields when the data is refreshed.