IF/CONTAINS Formula Question

Options

Right now I'm trying to experiment with pulling information from one sheet to another based on if the creation day and submitter match, and I'm struggling to get it to work. First I was getting an "INVALID DATA TYPE" error, so I tried starting from scratch but now I'm getting an "INCORRECT ARGUMENT" error, so I guess progress?? Here's what I have so far:

=IF(AND(CONTAINS(DAY(Created@row), DAY({Master Document Request Submittal Date})), CONTAINS([Created By]@row, {Master Document Request Submitted By}), {Master Document Request ID}, ""))

The logic is that if the current sheet Created Day matches the Master Doc row created Day AND the current sheet submitter matches the Master Doc row submitter, it'll pull the ID # from the Master Doc.

If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Best Answer

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 03/26/24 Answer ✓
    Options

    Hi @bisaacs , You'll want to explore the INDEX/COLLECT approach (in situations where you only have 1 condition, you could also use INDEX/MATCH). Search in the Community for some great discussions. In your case, INDEX/COLLECT would look like:

    = IFERROR(INDEX(COLLECT({Master Document Request ID},{Master Document Request Submittal Date},Created@row,{Master Document Request Submitted By},[Created By]@row),1),"")

    A quick explanation:

    • COLLECT() returns a range of the values that meet the criteria.
    • INDEX() tells SS which value from the range you want from COLLECT(). Since you will only have, at max, one value that meets the criteria, we are simply returning the first (and only) value.
    • IFERROR() will set the value of the cell to "" if nothing is returned which would produce an error since you can't take the "first" of "nothing".

    A few notes about the approach you were attempting:

    • IF() cannot accept ranges. It only operates on single values
    • DAY() returns the numerical value for the day of the month. so the 15th of March and the 15th of April will both return "15". You lose the month and year.

    I hope this helps. Good luck and be well!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

Answers

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 03/26/24 Answer ✓
    Options

    Hi @bisaacs , You'll want to explore the INDEX/COLLECT approach (in situations where you only have 1 condition, you could also use INDEX/MATCH). Search in the Community for some great discussions. In your case, INDEX/COLLECT would look like:

    = IFERROR(INDEX(COLLECT({Master Document Request ID},{Master Document Request Submittal Date},Created@row,{Master Document Request Submitted By},[Created By]@row),1),"")

    A quick explanation:

    • COLLECT() returns a range of the values that meet the criteria.
    • INDEX() tells SS which value from the range you want from COLLECT(). Since you will only have, at max, one value that meets the criteria, we are simply returning the first (and only) value.
    • IFERROR() will set the value of the cell to "" if nothing is returned which would produce an error since you can't take the "first" of "nothing".

    A few notes about the approach you were attempting:

    • IF() cannot accept ranges. It only operates on single values
    • DAY() returns the numerical value for the day of the month. so the 15th of March and the 15th of April will both return "15". You lose the month and year.

    I hope this helps. Good luck and be well!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey Scott,

    Thanks for this explanation!

    Initially I was trying to use the default Smartsheet "Created" column for the date reference, however that didn't end up working. I ended up having to create a separate Date column that just pulled the date from the "Created" column and use that as a reference in the formula, and then it worked perfectly!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!