Finding the earliest delivery date based on Material code as the criteria using MAX(COLLECT

Options

I have a source sheet which is basically a delivery schedule for all materials with multiple delivery dates, what I want to pull to the target sheet is the earliest delivery date of that material code. Currently I have the following formula which doesn't seem to work!

=MAX(COLLECT({EDD}, {STYLE}, [MATERIAL CODE]@row), 1)

EDD (Delivery date column in source sheet), Style ( Material code column in source sheet), Material code row in Target sheet.

For context there are multiple delivery dates per material code in the delivery schedule so I only want to pull the earliest delivery date based on that material code.

Hoping all makes sense any help would be appreciated!

Thanks again

Elliot

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi @Elliot Hamilton

    If you want the earliest, then you should use MIn instead of MAX. MAX will return the latest date.

    Watch out also on what kind of earliest date you want to return. The one that ever happened, or the next one to come. Would change the COLLECT a little bit.

    =MIN(COLLECT({EDD}, {STYLE}, [MATERIAL CODE]@row))

    Should do it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!