COLLECT with Date criteria

KarineMul22_TPg

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

Nick Korna

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! 🙂

KarineMul22_TPg

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))), "-")

Nick Korna

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! 🙂

KarineMul22_TPg

Thank you Nick, but it's still coming back with #UNPARSEABLE :(

I suspect it's the date part but cannot find the right way

Nick Korna

There was an extra bracket in there (just before the date range/criteria) - try the (now edited) formula.

KarineMul22_TPg

Brilliant, it works! Many thanks

Nick Korna

No problem, glad to have helped!