Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Issues with Max Collect formula and OR condition

I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in date columns) based on whether the status is 1 of 2 values (Draft or "Submitted for Approval"). I've tried with the contains but just not working.

This is the formula I'm using. It works with one value, Draft. I need to say give me the value if it is Draft or Submitted for Approval. Thanks for the help.

MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"))

Best Answer

  • ✭✭✭✭✭
    Answer ✓

    Max() will accept multiple sets of values - try:

    MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"),COLLECT({ReportAsOf}, {RepStatus}, "Submitted"))

    If that doesn't work, you can always add a helper column with an if(or()) to force a single value if RepStatus is Submitted or Draft and then run your collect() call on that.

Answers

  • ✭✭✭✭✭
    Answer ✓

    Max() will accept multiple sets of values - try:

    MAX(COLLECT({ReportAsOf}, {RepStatus}, "Draft"),COLLECT({ReportAsOf}, {RepStatus}, "Submitted"))

    If that doesn't work, you can always add a helper column with an if(or()) to force a single value if RepStatus is Submitted or Draft and then run your collect() call on that.

  • Brilliant! It worked perfectly. Thank you so much!


    Susan

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions