Dynamic referencing
Hi there, I'm a new user to Smartsheets and trying to replicate a function I created in Excel a while back. The end-goal is to make a dynamic budget sheet, where I fill in some data in the first few columns which will make the predetermined amount appear in certain cells of certain months. Let's say I have a quarterly expense of 100,00 for a year, I'd fill in "100,00" in the [Amount] Column, "Jan-24" in the [Start Date] column, "4" in the [Repetition] column and "2" in the [Interval] column. This would return the number 100,00 in the named month columns Jan-24, Apr-24, Jul-24, Oct-24 and Jan-25.
In excel the formula would be as follows:
=IF(AND(F$1>=$C4; F$1<=EDATE($C4; ($D4-1)*($E4+1))); IF(MOD(YEAR(F$1)*12+MONTH(F$1)-(YEAR($C4)*12+MONTH($C4)); $E4+1)=0; $B4; 0); 0)
I've already had MS co-pilot have a go at this but without results. Does anyone know if this type of cell referencing is at all possible in Smartsheet?
Answers
-
How exactly are you getting those particular months out of the number 4 being in the repetition column?
-
Hi Paul, The idea is that the interval value is used to determine empty cells, thus extrapolating the later values and placing the Amount in the particular months. I.e.: start date is Jan-24, the Interval is 2, so the subsequent Amount should be noted three months in advance, namely in Apr-24. The formula does this two more times with the last Amount being noted in Jan-25.
-
Now that your original post has been edited, can you clarify why you are using both an Interval column and a Repetition column? It seems redundant based on the logic you explained in your last comment.
-
Hi Paul,
Thanks for your question. "Repetition" determines the amount of times the Amount must appear in the forecast. "4" means the Amount must appear 4 times, "1", once, andsoforth. I use this variable in my forecast to discern between (for example) a singular expense and a recurring expense.
"Interval" determines the amount of (empty) months in between repeating months. I've included a screenshot of my excel, hopefully that will clarify?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!