Below is a summary of questions and answers from Smartsheet community members.please use this form.See questions and answers from Smartsheet community members at.
Summary of user questions
Andrew Magoon asks about setting up an s-expression that references the Key Accomplishments and Upcoming Key Activities columns in another sheet based on date.
Summary of jmyzk_cloudsmart_jp's answer
jmyzk_cloudsmart_jp suggests using the WEEKNUMBER() function to check if the date in other_sheet falls within this week.
Question and answer details
reference: public demo
Andrew Magoon ✭
I would like to set up a formula that references the Key Achievement and Upcoming Key Activity columns in another sheet based on the date.I currently have it set to fill out the original sheet by every Friday.I can use the index formula, but I'm having a hard time creating the date logic.Any suggestions?I need to find dates for this Friday and enter key achievements and upcoming activities by week based on a specific workstream name.jmyzk_cloudsmart_en ✭✭✭✭✭
@Andrew Magoon
Use the WEEKNUMBER() function to check if dates in other sheets fall within the current week.example:
=INDEX(COLLECT({key accomplishments range of other sheet}, {date range of other sheet}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())), 1)By the way I learned in @J. Craig Williams' comment below to use IFERROR to avoid "Invalid Data Type".
https://community.smartsheet.com/discussion/15846/invalid-data-type-formula-return
For more information,demo dashboard.
Andrew Magoon ✭
… thank you!jmyzk_cloudsmart_en ✭✭✭✭✭
Delighted! 😁
Conclusion
jmyzk_cloudsmart_en showed how to use the WEEKNUMBER() function to check if a date in another sheet falls within the current week and provided a concrete formula example.