Lab Create: Using the Retain Feature

Estimated reading time: 9 minutes

Overview:

When using Interject to pull data into your spreadsheet, the report area will be cleared before new data is inserted. In some situations, you may want to bypass the clear action and retain some formulas, since pulling could remove formulas you have on other columns related to certain data rows. This is especially true when you are budgeting or forecasting with historical numbers. The report may have history on the left and your forecasting formulas and numbers will be input on the right. This lab will show you how to update the data in a report without deleting rows that must be retained.

The Retain feature relates to reports using the ReportVariable() or ReportRange() functions. Since Interject must still update your data, it must determine which columns act as the key values, so it can place the correct data in the correct row.

When Interject finds new data, it will add a new row and populate the data. Using the retain feature, Interject will never delete a row, it will simply zero the amounts.

When a new row is added, there are two options: 1) to insert the new row at the bottom of the section or 2) to insert the row based on the sorting of the key values columns. The former is the default, since adding to the bottom of the section helps avoid adding new rows between your formulas. The lab below will walk through these and other steps on how to use the retain feature operates in your reports.

Use the PL Trend report for this lab.

If you are following the Training Labs, this report file can be found in the Report Library at Training Labs > Lab 5 Advanced Features > Lab 5.5 Using the Retain Feature.

Create Simple Formulas:

To start, copy the columns L-N in the PLTrend tab and paste them into Column P-R.


Then, unfreeze the sheet.


Clear out the cell range P2:R5 from the new columns so new data does not populate on the right side. This is also a good time to clear out the extra input spaces in the P20:R26 cell range.


Now input 7002 into Location and 2002-05 into Month. Then Pull the report.


Now, with the blank section to the right, we will create a projection based on the previous 3 months.

First, in cell Q26, input Forecasting Formulas. Then, edit the formula in P27 to be =IFERROR(EOMONTH(DATEVALUE(M23 &”-01”),1),”-“). The following month formulas are set to use this formula and will update accordingly.


Project a 10% increase to every value. First select cell P29 and input the formula =L29 * 1.10. Copy cell P29 to the clipboard. Next highlight cells P29:R152. Finally, right-click on the area and copy the formulas.


Without RetainedRowColumns:

If the accounts numbers were to change, the 10% increase would no longer be accurate since the data is old. Re-pull the data to update your numbers.


Without using the RetainedRowColumns feature, if your formulas are on the same rows as the data detail rows, the formulas get deleted when pulling updated data.


Note: The reason that some of the rows do not lose their formulas is because they are what are considered summary rows. A ReportVariable() also contains detail rows which are rows that get deleted on a pull or clear.

With RetainedRowColumns:

To fix this, use the RetainedRowsColumns to retain the rows and your formulas.

To do this, start by inputing =L29 * 1.1 into cell P29 again and then copy the formula into cells P29:R152 like before.


Now, click on the cell G16, which contains the ReportVariable() formula. Now click the fx button to pull up the function wizard.


Now scroll down to the RetainRowColumns. The RetainRowColumns argument expects a single string of comma delimited names of columns that will be retained after a data pull. Instead of entering the string, the jCombine() helper function is helpful as it will concatenate the column names for us.

Enter jCombine((F2:G2)) in the field. This will retain all rows that have a value in the Segment1 or Segment1Name columns.


Now, if the data changes again, you need to Pull the report again.


Notice that the formulas are still intact and accurate to the new data.

jCombineIf()

Now we will setup a jCombineIf() function in place of the jCombine. The jCombineIf function concatenates strings just like jCombine, but only does so if a certain critiea is met. In this case, we want to only retain the columns if a certain field is marked “Yes”. To do so, we will replace the jCombine with a jCombineIf and include this critiea.

Step 1: Enter “Retain: in cell D20 and “Yes” in F20.


Step 2: Next, click on cell G16 and change the “jCombine” to “jCombineIf”. Click on the Function Wizard button while the cursor is in the jCombineIf.


Step 3: Enter “F20:G20” for CriteriaRange, “F2:G2” for SelectedRange, and “Yes” for CriteriaValue. This will only concatenate the strings in F2:G2 if there is a “Yes” in the corresponding cells F20:G20.


Step 4: Pull the data again.


Notice the values are still retained.


Step 5: Finally, delete the “Yes” in cell F20 and pull the data again. Notice the formulas get deleted.