COLLECT with Date criteria
Hello,
Here is the formula I need help with please:
=COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},= "FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)))
{2046 - B1 Gantt Project Plan WPincomments} is the column where the data I want to collect is
{2046 - B1 Gantt Project Plan Task} is the column where my 1st criteria can be found
= "FE Sign off required (On QASOR)" is my 1st criteria
{2046 - B1 Gantt Project Plan Date} is the column where my 2nd criteria can be found, a date column
< DATE(2023, 7, 28) is my second criteria
Thank you
Best Answer
-
You need to make a few alterations for JOIN to work:
=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")
This should give you all the results in a single cell, separated by dashes.
An example of it in action (with columns rather than cross sheet refs):
Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂
Answers
-
As COLLECT doesn't work on its own, I have added JOIN but still has the same issue
=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},="FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28))), "-")
-
You need to make a few alterations for JOIN to work:
=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")
This should give you all the results in a single cell, separated by dashes.
An example of it in action (with columns rather than cross sheet refs):
Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂
-
Thank you Nick, but it's still coming back with #UNPARSEABLE :(
I suspect it's the date part but cannot find the right way
-
There was an extra bracket in there (just before the date range/criteria) - try the (now edited) formula.
-
Brilliant, it works! Many thanks
-
No problem, glad to have helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!