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

@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

Use a subcalculation 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
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!

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

@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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!