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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!