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 endgoal 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, "Jan24" 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 Jan24, Apr24, Jul24, Oct24 and Jan25.
In excel the formula would be as follows:
=IF(AND(F$1>=$C4; F$1<=EDATE($C4; ($D41)*($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 copilot 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 Jan24, the Interval is 2, so the subsequent Amount should be noted three months in advance, namely in Apr24. The formula does this two more times with the last Amount being noted in Jan25.

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
Check out the Formula Handbook template!