Help With Collect Function

Texangeek

I have a sheet that has three columns - DueDate, Status and "Status by Color" respectively. Based on the entries in the status column the "Status by Color" cell is assigned the color Red, Green or Blue.

My need is to identify the 5 oldest 'overdue' dates within the DueDate column. I achieved this using the following expression:

=IF([DueDate]@row < SMALL([DueDate]:[DueDate], 6), 1, 0)

This works correctly, however the answer which it returns is based on all values in the DueDate column, including those that are marked as "Complete". I need to limit the answer to the 5 oldest dates that are "Not Complete" (colored red). Someone advised that I could do this by including the COLLECT function in my expression - intent being to limit the cells considered within the SMALL function to only those that are red.

To achieve this I created a Checkbox column that is set to TRUE (1) if the status is red and FALSE (0) if it is not. Based on the third example in the COLLECT function help page, my modified expression is:

=IF([Due Date]@row < SMALL(COLLECT([DueDate]:[DueDate], [Red:Red], 1), 6), 1, 0)

As far as I can tell this is correct however the result is returned as #UNPARSEABLE.

I'd appreciate advice as to what I'm doing wrong!

Thanks in advance.

Paul Newcome

I would suggest using something like this...

=IF(AND([DueDate]@row < SMALL(COLLECT([DueDate]:[DueDate], [Status by Color]:[Status by Color], "Red"), 6), [Status by Color]@row = "Red"), 1, 0)

John Reynolds

Not sure if you can help, but I have one column called group, and from a form question, I have six sub-groups based on branch logic in the form. As a result, each row would have on group, and only one entry in one of the sub-groups, based on the drop-down menu response. Is there a way to move the six columns of sub-groups over to the side, and then develop one column that contains the only sub-group entry possible in the row? I tried Join/Collect with not luck, but I am admittedly a neophyte. Thanks for any help!

Chamudi Withanawasam

I too have a similar question where I need the task relating to the minimum date value in the project (earliest date) where the task is not marked complete and also there are no predecessors for the task (To filter out the header tasks); basically next task that is plaanned to start. I tried few options:

=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1), {TEst Project Perdecessors}, <>))), {TEst Project Start}, 0))))

=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1))), {TEst Project Start}, 0))

=INDEX({TEst Project Task Name}, MATCH(MIN(COLLECT({TEst Project Start}, {TEst Project % Complete}, NOT(1), {TEst Project Parent}, >=1)), {TEst Project Start}, 0))

First one gives me an error while the other two give me the header task as the result.

Is it something to do with the COLLECT function?

Paul Newcome

@Chamudi Withanawasam Try an INDEX/COLLECT (without the MATCH).

=INDEX(COLLECT({Test Project Start}, {Test Project % Complete}, @cell <> 1, {Test Project Predecessors}, @cell = ""), 1)

Chamudi Withanawasam

Hi Paul,

It still doesn't work. Your equation doesn't consider the earliest start date (minimum value) that satisfy the requirements.