# 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)

• ✭✭✭✭✭✭

@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

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

• 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?

• ✭✭✭✭✭✭

@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

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

• edited 03/01/22

Got it! Thank you @Jeff Reisman

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman

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

• @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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!