Index/Match formula based on date range?

Andrew Magoon · 03/31/23

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

.

jmyzk_cloudsmart_jp · 04/11/23

Hi @Andrew Magoon

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.

https://app.smartsheet.com/b/publish?EQBCT=5d59e9a3de064000a9592d1870617fcd

Mike TV · 03/31/23

@Andrew Magoon

Switch to INDEX/COLLECT instead. It will allow you to specify criteria during indexing data into your sheet.

Andrew Magoon · 04/05/23

@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.

jmyzk_cloudsmart_jp · 04/09/23

Hi @Andrew Magoon

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.

Reference Other Sheet Data Based On Date Range.png

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

Andrew Magoon · 04/11/23

Hi @jmyzk_cloudsmart_jp

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?

jmyzk_cloudsmart_jp · 04/11/23

Hi @Andrew Magoon

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.

https://app.smartsheet.com/b/publish?EQBCT=5d59e9a3de064000a9592d1870617fcd

Andrew Magoon · 04/12/23

@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

jmyzk_cloudsmart_jp · 04/13/23

Happy to help!😁

Index/Match formula based on date range?

Best Answer

Answers

Help Article Resources

Categories