Help With Collect Function

Texangeek
Texangeek ✭✭
edited 04/16/20 in Formulas and Functions

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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!