Can't identify my Formula error

PStowers

Can anyone suggest what is wrong with my formula? I get an #INVALID OPERATION result.  I've looked at it for too long and can't find the issue. Thanks.

=IF(NOT(ISBLANK([Order Date]1)), "Ordered", IF([READY TO ORDER]1 = 1, "Ready to Order", IF([CPM Complete]1 = 1, "CPM Need Approval", IF(NOT(ISBLANK([Vendor Quote requested (Date)]1)), "Vendor Quote Requested", IF(AND(NOT(ISBLANK([Aaron O'brien approval]1)), NOT(ISBLANK([Jimmy Yang approval]1)), [James Keng approval]1 = "Approved"), "PIF Approved", IF(AND([Market Study Complete]1 = 1, ISBLANK([Aaron O'brien approval]1)), "PiF Needs Approval", IF(AND([PM Recv'd Request]1 = 1, [Market Study Complete]1 = 0), "Mkt Study & Sourcing ", IF([PM Recv'd Request]1 = 0, "New Project"))))))))

James McCombs

Hey PStowers,

I think maybe you got a little tripped up on the ISBLANK and the NOT(ISBLANK) statements, so I did up a test sheet and rewrote the formula:

 

=IF([PM Recv'd Request]1 = 0, "New Project", IF([Market Study Complete]1 = 0, "Mkt Study & Sourcing", IF(OR(ISBLANK([Aaron O'Brien Approval]1), ISBLANK([James Keng Approval]1), ISBLANK([Jimmy Yang Approval]1)), "PIF Needs Approval", IF(ISBLANK([Vendor Quote Requested (Date)]1), "Vendor Quote Not Yet Requested", IF(ISBLANK([CPM Complete]1), "CPM Not Complete", IF(ISBLANK([Ready to Order]1), "CPM Need Approval", IF(ISBLANK([Order Date]1), "Ready to Order")))))))

From what I was reading in your formula, it seemed like all 3 approvals were needed in order to move forward with the request, so I made it an OR statement. If you only need either Jimmy Yang's or James Keng's approval to move forward after Aaron, then you can rewrite that segment as:

IF(OR(ISBLANK([Aaron O'Brien Approval]1), AND(ISBLANK([James Keng Approval]1), ISBLANK([Jimmy Yang Approval]1))),

 

Also, from what I was reading it sounded like your formula was showing CPM Needs Approval if the CPM Complete cell was filled in, which I wasn't really clear on, but I think you can tweak it if need be - I don't know all your processes. 

 

I hope this works for ya!

 

 

Paul Newcome

Is the #INVALID OPERATION error in any of the referenced cells?