Dynamic referencing

GusvO

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?

Paul Newcome

How exactly are you getting those particular months out of the number 4 being in the repetition column?

GusvO

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.

Paul Newcome

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.

GusvO

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?