Trying to get some formulas to work to make a month timeline


I have a number of sheets that I am relaying on using this timeline sheet for. This is for a commission workplace and individual commissions. In the core sheet, I track the date of sale. Which that gives me a YEAR / Month. Then, I want to assign it a Current Month, Last month, 2 months ago, and so on.

My goal was to essentially never touch this sheet again, and create a timeline going back up to 60 months ago. (Do I dare say that we may need to go further in the future?)

So I am looking for a way to auto populate every column besides the first 2.

Any recommendations?



  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @Mtmoroni

    I overcame this issue by setting up a secondary sheet that contains a single row of data that references the current month, this is automatically copied across on a fixed day of the month to populate the other sheet. As that single row can be based on a formula referencing the current date it becomes the only moving part.

    Also this may not apply but if you have a lot of reference / calculation data it may be worthwhile carrying out calculations on the other sheet and then copying that info across to the final data sheet as well, if you need to archive old data etc. you may find some of the older month information zeroing out as you remove the older data. Just something to bear in mind.

    I hope that helps



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!