Can't identify my Formula error

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"))))))))

Find more posts tagged with

Formulas

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?