Help with a weekly updated formula

Hello! I'm still a beginner smartsheets user, and while I am decent with excel the live updates is still very new. I'm trying to create a formula that will pull information on a weekly basis, M-F, that updated for each week and didn't pull from the previous week at all. For example in this screenshot, I would like a formula where I can select the whole column, but only grab information from this week, 1/26 through 2/1. And then as soon as we hit 2/2, I'd like it to update to information from 2/2 through 2/8. Is this possible and can you help me with what that formula would look like?

Answers

  • io_DR
    io_DR ✭✭
    edited 01/31/25

    There may be other ways to do this - but the simplest way to me would be to add a helper column and add the formula:

    =WEEKNUMBER(Day@row)

    The in the Source sheet - add a Today column and have an automation run daily that will update the date on every row to Today's date. This is important if you don't edit the sheet daily. There's a little issue with Smartsheet and using the TODAY() function when you are not in your sheet on a regular basis.

    I would add the helper column in your source sheet as well =WEEKNUMBER(Today@row)

    Then when you do the Index/Match formula you are matching the Weeknumber from your Target sheet to the Weeknumber in your source sheet. This will create an "automatic" changing of the data pulled.

    Does that make sense?

    I would need a little more information about what is in your source sheet - and I could help you with the INDEX/MATCH or INDEX/COLLECT formula if you need it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!