Return latest value from another column's latest rows

Manuel Service

Hello, I have a sheet in Excel I'd like to move to smartsheet to work with a client's team.

The only problem I have is with the fields "Previous Cumulative Partner Discount" and "New Cumulative Partner Discount" as they have formulas that involve each other column in theirs and in Smartsheet it returns the circular reference. To better explain:

"Previous Cumulative Partner Discount" formula uses previous rows values from the "New Cumulative Partner Discount" column to get the latest value out of it depending on the "BP Name" column. And the "New Cumulative Partner Discount" column uses "Previous Cumulative Partner Discount" column value from the same row to find the difference between it and another column.

I tried using both COLLECT and INDEX formulas but both use only entire columns as range instead of specific rows like Excel. I assume this is why it's getting the circular reference error.

I've attached the dummy Excel file if it helps see what I'm trying to achieve. (Columns I and L).

Thank you

Discounts Dummy.xlsx

mcarlson

In Smartsheet, the circular reference error occurs because formulas referencing each other across rows or columns can’t dynamically calculate the way Excel allows with iterative row references. While Smartsheet doesn’t natively support dynamic referencing between rows with circular dependencies, here’s a workaround approach to achieve similar results:

Workaround Approach

1. Helper Columns for Cumulative Calculation:
   • Create two helper columns to calculate cumulative values separately. For example:
     • Helper Column 1: Stores the most recent cumulative discount by looking up the previous row’s New Cumulative Partner Discount.
     • Helper Column 2: Calculates the difference between the cumulative discount and the base value from the other column.
2. Use the INDEX and MATCH Functions to Reference Specific Rows:
   • Use INDEX and MATCH in a way that prevents circular dependencies by only referencing prior rows.
   • Here’s an example of how to adapt your formulas:
   • excelCopy code=IF([Row Number]@row = 1, [Starting Discount], INDEX([New Cumulative Partner Discount]:[New Cumulative Partner Discount], MATCH([BP Name]@row, [BP Name]:[BP Name], 0) - 1))
     • In this formula:
       • Replace [Starting Discount] with the initial discount value.
       • MATCH finds the previous occurrence of the BP Name and retrieves the value from New Cumulative Partner Discount for that row.
   • excelCopy code=IF([Previous Cumulative Partner Discount]@row > 0, [Previous Cumulative Partner Discount]@row + [Discount Change], [Initial Discount])
     • This formula assumes that [Discount Change] is a column where changes in the discount are listed.
     • Use a base [Initial Discount] value if no previous value exists.
3. Setting Up Conditions to Avoid Circular References:
   • To avoid referencing the same row, adjust the formulas to pull values only from rows with confirmed values (e.g., use helper columns to validate rows before they are referenced).

Alternative Approach with Manual Updates

If the calculations are too complex or rely heavily on dynamic referencing, you might consider using Excel for this portion and then linking the output data to Smartsheet. This would allow you to leverage Excel’s robust circular reference capabilities without recreating the logic in Smartsheet.

These workarounds should help manage cumulative values without triggering circular references in Smartsheet. Let me know if you'd like a specific formula example based on more details!