Sage Intelligence Reporting: Copying an Excel Formula to New Rows

When you run a report and want to customize it and add additional data fields within Excel, you may want to add acolumn with a formula to perform a calculation on the rows of source data that are pulled through from the ERP or Accounting Database. If you later run the report and there are additional rows, the formula does not copy down to the new rows of data that have been pulled through from the database.

The ExtendFormulaetoUsedRows is an add-in that you configure in the Report Manager that will allow you to automatically extend formulas to any new rows of data each time your report runs. For Example if you needed to calculate Gross Profit amount when you run a report. You would add the calculation Total Sales – Total Cost. And you could then auto fill the formula to the remaining rows of data.

If however you ran the report and the number of transactions in the source sheet increased, the formula to calculate the GP would only extend as far as the row you auto filled it to when you originally set it up. Using ExtendFormulaetoUsedRows Add-In will automatically complete the calculation for the number of rows in the source sheet.

Configuring the ExtendFormulaetoUsedRows Add-In Function

Rows are discarded or hidden based on a specified columns value.

  1. Open the Report Manager. Run the Report. In the Excel source sheet add the calculations/ columns required after the last column of source data. The formula only needs to be added to the first row.
  2. Go back to the Report Manager. Select the report that you ran and want to use the ExtendFormulaetoUsedRowsadd-in on.
  3. Enable the Show Advanced properties of the report at the bottom of the properties window.
  4. Select the Run Add-Ins ellipses button.

  1. Select the Add-In Library : Alchemex Add-In 1.
  2. Select the Add-In Library Module : General Excel extensions Module.
  3. Select the Add-In Function : ExtendFormulaetoUsedRows.

  1. Click OK. The Specify Function Parameters window will appear.

Parameter / Description / Example
Destination Sheet / Type the Microsoft Excel worksheet name where the data is located. / Sheet1
The Column to begin at / Type in the Microsoft Excel column number, for example Z will be 26. / 26
The first row to begin copying to / This is the first row to begin copying the formula to / 3
The Excel Range to Copy / This is the cell range that you would like to copy to the additional rows / Z2
The Column used to define the used row range / This is an Excel column that can be used to determine if the row has data. Something like an invoice number, employeeid / 2
  1. When you have entered your details, select OK. You will notice that a string has been added into the Run Add-Ins box in the properties window of your report.
  2. Save the Excel Template to the Report in the Report Manager.
  3. The add-in will now automatically run each time the report is run.