Row based function

Trying to create a calculation. I have a column with the starting count and a column that needs to show the remaining amount. The rother sheet columns will sporadically have values added and I want these to all subtract from the total and display the remaining inventory in the remaining amount column. Want to know if there is a way to do an @row type function that will work when new columns are added vs having to keep updating the formula. This is what i created with the original columns, but we are needing to keep adding columns and this does not carry over to the new one.


=SUM([Allocation Volume KG]@row - [HK#1 4x5s]@row - [HK#1 1x5s]@row - [HK#1 CY20]@row - [HK#2 4x5s]@row - [HK#2 1x5s]@row - [HK#2 CY20]@row - [HK#3 4x5s]@row - [HK#3 1x5s]@row - [HK#3 CY20]@row - [HK#4 4x5s]@row - [HK#4 1x5s]@row - [HK#4 CY20]@row - [CN#1 4x5s]@row - [CN#1 1x5s]@row - [CN#1 CY20]@row - [CN#2 4x5s]@row - [CN#2 1x5s]@row - [CN#2 CY20]@row - [CN#3 4x5s]@row - [CN#3 1x5s]@row - [CN#3 CY20]@row - [CN#4 4x5s]@row - [CN#4 1x5s]@row - [CN#4 CY20]@row - [CN#5 4x5s]@row - [CN#5 1x5s]@row - [CN#5 CY20]@row)

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓


    @Nicol Sloon Here's how you do that:

    Change your formula to SUM the values of the RANGE of columns that you're subtracting from Allocation Volume KG before doing the subtraction. Put a dummy column at the end of the range containing 0, and any new columns you add should go anywhere to the left of the dummy column.

    Your formula in the Remaining Amount column would look like this:

    =[Allocation Volume KG]@row - SUM([HK#1 4x5s]@row:[DummyColumn]@row)

    Here it is in action on my test sheet:

    The "Newly Added Column" to the left of the DummyColumn automatically becomes part of the range to SUM, and therefore gets subtracted from AmtStart, updating the AmtRemain.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!