# Dynamic referencing

Options
✭✭
edited 01/08/24

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!