Can COLLECT Criteria use text values?

I am trying to find the earliest date in a range of dates. But I only want to consider dates that have a certain text value in a corresponding cell.

Here is the formula I'm trying to get to work:

=MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]@row:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]@row), ="ON/LIVE")

[Prod Date [n] (copy)] range are dates

[Prod Date [n] - (ON / OFF / DARK / LIVE) (copy)] range are text

I get an "#INCORRECT ARGUMENT SET" error with this formula.

Would appreciate any help on what I'm doing wrong here.


Thanks!

Answers

  • Randy Saad
    Randy Saad ✭✭✭

    You have it, but you are using two @row ranges referencing different rows. If you your formula is in the row of dates and the row containing your "ON/LIVE" values is row 4, your formula would be:

    =MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]4:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]4), ="ON/LIVE")

  • twarner
    twarner ✭✭✭✭

    Hi Randy -

    Thanks for your answer. However, ALL of the data lives on a single row. This is a sheet of project status and each row represents a Project. Each project row has 8 possible deployment dates and each deployment date has a "type" field. So I am trying to find the earliest deployment date with the "ON/LIVE" deployment "type".

    Hopefully that makes sense. Perhaps it's because everything is on the same row that the formula is puking on me?

    Thanks,

    Tony

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!