INDEX COLLECT Multiple Values that match a date range
Hello community!
I want to return the name of every BROADCAST that falls within the date range where I have a contractor coming on site at my office.
In my contractor sheet I have a start and finish date column.
Contractor Start: 02/20/24 Contractor Finish: 02/21/24
In my Broadcast sheet, I have over 100 broadcasts spanning over the year.
My first formula in my contractor sheet tells me how many broadcasts clash with my contractor date range - this is working perfectly.
=COUNTIFS({Start}, [Start Date]@row >= @cell, {Finish}, [Finish Date]@row <= @cell, {B/M.}, "BROADCAST")
The value returned is 3. As there are 3 values (3 broadcasts) that clash, I want to somehow return the name of all 3 of these broadcast values. Here is the formula I have:
=INDEX(COLLECT({Broadcast}, {Start}, [Start Date]@row >= @cell, {Finish}, [Finish Date]@row <= @cell), 1)
I need to add an if statement to this formula so that it only reads the dates that are broadcast items (I have a helper column for this I just need to know how to utilise it within an index collect formula).
Then I need it to return all 3 values in a list.
Does anyone know if this is possible?
Best Answer
-
The first thing to do is to add another range/criteria set to evaluate the helper column that indicates whether or not it is a broadcast so that you can further filter by broadcast vs not.
Then you need to switch from an INDEX to a JOIN. INDEX will only ever pull one instance. The JOIN will display multiple instances.
Answers
-
The first thing to do is to add another range/criteria set to evaluate the helper column that indicates whether or not it is a broadcast so that you can further filter by broadcast vs not.
Then you need to switch from an INDEX to a JOIN. INDEX will only ever pull one instance. The JOIN will display multiple instances.
-
Amazing thanks for your help again Paul! I'll figure out the rest
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!