#Incorrect Argument??

khankoff

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.

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

khankoff

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.

 

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å

Excellent!

Happy to help!