Will IF(MAX(Collect work?

Options

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!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!