#Incorrect Argument??

khankoff
khankoff ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to build an IF statement that will change the value in a Status column.  The Status column has three values in the dropdown (Not Started, In Progress, Complete).  I would like to leverage two adjacent Date columns (Scan Started, Scan Completed) to auto-change the values in the Status column, but can't seem to get it to work.  

I've gotten half of it to work by using this:

=IF([Scan Started]1 = "", "Not Started", "In Progress")

However, when I add another IF, I break the formula.  Wondering if I'm just barking up the wrong tree, and can't do this because of the range.  Here's what I built that returns #Incorrect Argument:

=IF([Scan Started]1 = "", "Not Started", "In Progress", IF([Scan Completed]1 <> "", "Complete", ""))

Can I make this work?  I'd really like for my users to just have to select either the Start or Complete Dates, and then let the worksheet do the rest.

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Ken,

    Try this.

    =IF([Scan Started]@row = "", "Not Started", IF([Scan Completed]@row <> "Complete", ""))

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • khankoff
    khankoff ✭✭✭✭✭
    edited 08/24/19

    Hi Andree - 

    Sorry to say it didn't work.  I thought it might be due to the 2nd part of the formula (having "Complete" before the ""), but after trying it both ways, it's still not working as desired.

    The formula does cause the Scan Started column to populate with Not Started.  However, what's happening is that putting a date in the Scan Started column results in the Scan Status changing from Not Started to blank.  However, if I then enter a date in the Scan Completed column, the Scan Status does convert to Complete.

    Here's the full formula now:  

    =IF([Scan Started]1 = "", "Not Started", IF([Scan Completed]1 <> "", "Complete"))

    I've attached an image to show you what it looks like.

    Thanks for trying to help me.

     

    SmartsheetImage.jpg

  • khankoff
    khankoff ✭✭✭✭✭

    Never Mind.  Figured it out with the help of Ben on the ProDesk.  Apparently, the order of operations was messing me up.  Here's what we did to change the formula, and now it works.

    =IF([Scan Completed]1 <> "", "Complete", IF([Scan Started]1 = "", "Not Started", "In Progress"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!