Create Calculated Fields in a Dynamic Excel List | CLEARIFY

Working with Calculations and Dynamic Lists in Excel

Since the data from a dynamic data source like QQube for QuickBooks is 'one living complete organism', calculations should not be created outside of the list area.

You can never count on a calculated value that is referenced to a particular cell, because the value in that cell may move upon data refresh.  It is not tethered.

You can however, create a calculated column that becomes part of the existing Dynamic List.  You can use normal Excel functions to produce the result.

When to Use Calculated Columns

• Get around the limitations of Calculated Fields in regular Pivot tables.  Specifically you can't write an IF statement based upon the value in a particular cell.
• Convert custom fields that contain text formatted as numbers - into actual numbers that can be used in calculations, or even a pivot table.  (See this Support Guide on Custom Fields.)

Creating a Calculated Column

Right click anywhere in the last column of your dynamic data range and choose Insert > Table Column to the Right

A new column will appear, that becomes PART of the range (and which you can rename).

Here you can use any formula, where the results will be repeated down the length of the column.

The formula used in this example was "if the item is "Cover -DB" then get me the Sales Amount, otherwise show 0.00"  =IF([@[Item Name]] = "Cover -DB",[@[Sales Line Sales Amount]],0)