Remaining Budget Column Formula
I have an intake form where we receive campaign budgets. We are trying to track when our campaigns as a whole start spending over our expected Overall Budget. I need a column formula that can calculate the remaining budget when a new campaign is added. Currently, the intake form is set up to have new campaigns be added at the top.
Below is a screenshot of an Excel version of this where the budget cell is just =[new budget] - [previous remaining budget] and the first row (CPN-1) is =[overall budget] - [campaign budget CPN-1]
Any formulas I saw that were similar included excel functions like ROW or OFFSET which Smartsheet doesn't have. I would love any advice or recommendations on how to solve this!
Best Answer
-
I am making a few assumptions here... if those are incorrect, this would need to be tweaked somewhat:
- Your sheet uses the same consistent ID scheme as in your screenshot. Missing IDs will not cause an issue, but different formats (even including spaces) will throw it off
- "Overall Budget" is stored in a field in the sheet summary. This doesn't have to be the case... it could be setup to work other ways.
Give this a try and see if it does what you want:
=[Overall Budget]# - SUMIFS([Campaign Budget]:[Campaign Budget], [Campaign ID]:[Campaign ID], VALUE(RIGHT(@cell, LEN(@cell) - 4)) <= VALUE(RIGHT([Campaign ID]@row, LEN([Campaign ID]@row) - 4)))
Answers
-
I am making a few assumptions here... if those are incorrect, this would need to be tweaked somewhat:
- Your sheet uses the same consistent ID scheme as in your screenshot. Missing IDs will not cause an issue, but different formats (even including spaces) will throw it off
- "Overall Budget" is stored in a field in the sheet summary. This doesn't have to be the case... it could be setup to work other ways.
Give this a try and see if it does what you want:
=[Overall Budget]# - SUMIFS([Campaign Budget]:[Campaign Budget], [Campaign ID]:[Campaign ID], VALUE(RIGHT(@cell, LEN(@cell) - 4)) <= VALUE(RIGHT([Campaign ID]@row, LEN([Campaign ID]@row) - 4)))
-
You are a rockstar! That worked! Thank you so much!!
-
Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!