Dynamic referencing

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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!