Will IF(MAX(Collect work?

RuthieRooks

Hello community,

I am using a MAX(Collect( formula to pull in a number from another sheet where I calculate the number of days we have to lock a project's files after completion - this is pulling into our project tracker. The origin sheet is calculating the countdown number based on one particular type of work request and the date requested to be completed. The MAX formula is grabbing the most recent date associated with matching project numbers between the tracker and work request sheets - each project number can have multiple entries, and not every project listed in my tracker has a result in the request sheet yet. The team viewing the tracker needs the countdown number of days to know when they have to lock the files of their active projects (before the countdown gets to "0").

The problem is where there are not matches in the request sheet, or N/A where the work request type does not trigger a countdown, MAX(Collect returns a "0" (zero). 0/Zeros are also accurate when a file has to be locked by today, so we are returning 3 instances where a result can be 0, but only one that requires action. I'm wondering if I should add an IF statement to MAX(Collect so that it populates "0" only when the origin field is at 0, and returns blank otherwise.

I tried a few variations on:

=IF(MAX(COLLECT({FSP_Countdown}, {FSP PN}, [Project Number]@row)){FSP_Countdown}="N/A", "")

but it's not right. The italicized formula is working as described above, and does give me correct results where other values are populated in the origin sheet. I will include screenshots if someone needs to see them for reference.

Thank you!

Paul Newcome

In that case, try this…

=IFERROR(INDEX(COLLECT({Source Sheet Number Column}, {Source Sheet Project Column}, @cell = [Project Number]@row), COUNTIFS({Source Sheet Project Column}, @cell = [Project Number]@row)), "N/A")

The above should output either the number or "N/A".

Paul Newcome

Are you able to provide screenshots for context?

RuthieRooks

@Paul Newcome I hoped it would be you to answer! Legendary ;)

The Tracker sheet: Project 454036 was processed today, so the countdown from 60 just started, while 589690 is overdue to be locked by 33 days. 472896 does not have a match on the origin sheet but pulling a "0" - I'd rather a blank or N/A type of response

Here, 591719 is indeed due to lock today, so it is correctly pulling a "0":

The origin/work request sheet: matches project number from tracker, a "processing" work request triggers the countdown based on the delivery date requested. Using below to calculate 60 day countdown:

=IF(CONTAINS("processing", [Check Off]@row), [Delivery Date Requested]@row + 60 - TODAY(), "N/A")

Does this help?

Paul Newcome

How exactly does the source sheet get populated? Will the newest row for the project ALWAYS be at the bottom?

RuthieRooks

@Paul Newcome yes, the source sheet gets populated from a form and newest entries are at the bottom. We have shared filters to hide requests marked completed.

Paul Newcome

In that case, try this…

=IFERROR(INDEX(COLLECT({Source Sheet Number Column}, {Source Sheet Project Column}, @cell = [Project Number]@row), COUNTIFS({Source Sheet Project Column}, @cell = [Project Number]@row)), "N/A")

The above should output either the number or "N/A".

RuthieRooks

Excellent - thank you! I do get a #INVALID COLUMN VALUE when there isn't a matching project number on the source sheet, which is not a big deal, and the 0's are now the actionable 0's we need. Everything else is N/A.

Thanks again, @Paul Newcome!

Paul Newcome

Happy to help. 👍️