Dynamic referencing

GusvO
GusvO ✭✭
edited 01/08/24 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • GusvO
    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
    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
    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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!