Collect Criteria not being filtered

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Collect Criteria not being filtered

RMRGSARMRGSA
edited 12/09/19 in Formulas and Functions

My nested COLLECT function's Criteria does not appear to be working because the formula runs into errors when a duplicate date is found before the actual date that should be being pulled. 

 

=INDEX([Baseline Variance]5:[Baseline Variance]128, MATCH(MAX(COLLECT([Task Complete Actual]5:[Task Complete Actual]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker")), [Task Complete Actual]5:[Task Complete Actual]128, 0))

 

The [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker" should filter out the row that has an identical Task Complete Actual date because it does not have the "Tracker" criteria.

 

Excel example attached

Yellow cell should read "4" but doesn't because the duplicate dates in Actual Complete column for Tasks 6 & 7

Schedule Smartsheet.PNG

Comments

  • My current thinking is that the MATCH(MAX(COLLECT())) is returning the proper date but the Index isn't following the criteria I have in COLLECT. I suppose I could include an IF statement in INDEX to filter on Tracked tasks but I'm unsure of how exactly I would do that.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What type of column is your [Milestone Type Tracker]? Can you provide a screenshot of your SS version?

Sign In or Register to comment.