Index/Match formula based on date range?
Hi. I am looking at setting up a formula to reference another sheet's key accomplishments and major upcoming activities column based on a date. Right now it's set up to where we populate the original sheet by the Friday of each week. I've played around with getting the index formula to work but am struggling to build the date logic. Any suggestions? It would need to look for this week's Friday date and populate the key accomplishments and upcoming activities based on a specific workstream name week over week
.
Best Answer
-
If you are trying to find the if other_sheet's date belongs to this week, use the WEEKNUMBER() function.
Example:
=INDEX(COLLECT({key accomplishments range of other sheet}, {date range of other sheet}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())), 1)
I owe @J. Craig Williams's comment in the following discussion for using IFERROR to avoid "Invalid Data Type."
https://community.smartsheet.com/discussion/15846/invalid-data-type-formula-return
For details, please look at the demo Dashboard at the following link.
Answers
-
Switch to INDEX/COLLECT instead. It will allow you to specify criteria during indexing data into your sheet.
-
@Mike TV Thank you, that definitely gets me closer but the date formula is still in question to fit that part in. Really just looking at if DATE is within this week, return the key accomplishments from that row.
-
I would use a formula like this to get "key accomplishments" through Monday to Friday of the week belonging to a specified date.
=JOIN(COLLECT({key accomplishments}, {week}, week@row, {weekday}, AND(@cell > 1, @cell < 7)), CHAR(10))
Here, {week} refers to the week number, and {weekday} refers weekday number.
If you want to get a week's "key accomplishments" up to a specified date, you will use a formula like this.
=JOIN(COLLECT({key accomplishments}, {week}, week@row, {date}, @cell <= date@row), CHAR(10))
Please take a look at the demo dashboard for detail.
https://app.smartsheet.com/b/publish?EQBCT=155f86bc501c405ca7615b44a8347004
-
Appreciate your response. The only thing I am seeing is the way it's setup for us is we document key accomplishments for the entire week in one cell, so we don't document multiple days and only have the week end date (Friday) that covers the entire week. So the JOIN function I don't think is necessary. I've been playing around with the logic to be able to pull in only this week's accomplishments and would need something like IF today's date (which I can reference from sheet where I want this populated) is <1 day from that Friday (from key accomplishments page), then start the INDEX/COLLECT function to look to pull in those key accomplishments. It's the date formula that I am struggling with. Could there be another function that looks at the latest entry until there is a blank?
-
If you are trying to find the if other_sheet's date belongs to this week, use the WEEKNUMBER() function.
Example:
=INDEX(COLLECT({key accomplishments range of other sheet}, {date range of other sheet}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())), 1)
I owe @J. Craig Williams's comment in the following discussion for using IFERROR to avoid "Invalid Data Type."
https://community.smartsheet.com/discussion/15846/invalid-data-type-formula-return
For details, please look at the demo Dashboard at the following link.
-
@jmyzk_cloudsmart_jp This is definitely got me closer. thank you. There will need to be one more set of criterion for the LOB range to pull only the latest key accomplishments under marketable tower space but the below looks to be working. Thanks so much!
=INDEX(COLLECT({Key Accomplishments}, {Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {LOB}, [Column4]4), 1)
Column4 just specifies marketable tower space
-
Happy to help!😁
Help Article Resources
Categories
Check out the Formula Handbook template!
