Have not found this in the community, videos, courses, or formula template. CONTEXT: I have a dashboard that updates weekly. It has weekly data and a rolling 13 week chart. The date column is a Saturday (weekending date) and the team completes it when they come in Monday for the week prior). I have a data collection sheet where the data goes each week via form. I have a calculation sheet that has the most recent week's data, then 12 more weeks going back, but I can't figure out how to just get the date for another part (thanks to the prodesk!-I'll put that formula at the end in case someone else benefits from it). THE ASK: 1. a formula on the Metric sheet that searches the Source sheet, Week Ending column, and returns the most recent past Saturday and stays current through the year. 2. A way to duplicate that formula and modify it for the past 12 weeks after the most current one. More context: My metric sheet has references for the Weekending column {Week} the Week # {Week#} and Monday {Monday} because I've tried WeekNumber, Weekday, and Today functions and I'm still at a loss. I am currently going in and manually typing the dates in each week. Formula that the Prodesk helped me with below does return the right data from the previous weeks, but I also need one that just gives me the date. The -1 is last week, and I duplicated for -2 for two weeks ago etc. going up to -13 for thirteen weeks ago. =IFERROR(SUM(COUNTIFS({Who}, [Score Card for ...]@row, {Hazard}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), COUNTIFS({Who}, [Score Card for ...]@row, {Injuries}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))), 0)

I'm not sure what you're asking above. Sorry. I did get some help from Prodesk and this works if I change the number in () after today to the number of days such as 2 weeks is 14 (2x7); etc. =IFERROR(INDEX(COLLECT({Week}, {Week}, @cell >= TODAY(-7), {Week}, @cell <= TODAY()), 1) + "", "")

Search a data column and return the most recent past Saturday.

Answers

Paul Newcome ✭✭✭✭✭✭

02/27/24

Let's try working in the DISTINCT function:
=LARGE(DISTINCT(COLLECT({Week}, {Week}, @cell <= TODAY())), 1)

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Search a data column and return the most recent past Saturday.

Answers

Help Article Resources

Categories