Row based function

Nicol Sloon

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)

Jeff Reisman

@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.

Zach Zalowitz

Use a sub-calculation on a sheet, and in that formula that sums that (or does whatever math) cast a broad net in terms of columns needed, then the formula just subtracts that field... at least that's how I've done it in excel before.. .the issue here I think is that if the column doesn't exists on the SmartSheet, then it's just not there.... NET - likely not a fix, short of something like the above?

Jeff Reisman

@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.

Nicol Sloon

Got it! Thank you @Jeff Reisman

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321210#Comment_321210

Excellent! Glad it worked. I almost skipped over this post after seeing that maze of column names in your formula 😂

Nicol Sloon

@Jeff Reisman I am grateful you didnt....it was stumping me for some dumb reason! But now it makes so much sense haha. Thanks again!