IF formula error

AngeloV

Hi,

I have a big dropdown menu for "estimate status" and I need the column next to it to give me the person who is assigned depending on he status"

=IF([Estimate Status]@row = "Case Needed", "Riley", IF([Estimate Status]@row = "Design Underway", "Lester", IF([Estimate Status]@row = "Design Underway - Previously Proposed", "Lester", IF([Estimate Status]@row = "Design Underway - Previously Booked", "Lester", IF([Estimate Status]@row = "Case Needed for Drawing Verification/Update, New F1/Addendum Issued - Previously sent to Customer for Approval", "Riley", IF([Estimate Status]@row = "Case Needed for DD01 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD02 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD03 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD04 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD05 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD06 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD07 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD08 Change Request", "Riley", IF([Estimate Status]@row = "Design Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD01 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD02 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD03 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD04 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD05 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD06 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD07 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD08 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "Design Approved, NAM to Propose", "Riley", IF([Estimate Status]@row = "DD01 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD02 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD03 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD04 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD05 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD06 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD07 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD08 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD09 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD10 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "CO Approved, NAM to Propose", "Riley", IF([Estimate Status]@row = "Case Needed for Drawing Verification Update New F1 Addendum Issued Previously Proposed", "Riley", IF([Estimate Status]@row = "Proposed", "Customer", IF([Estimate Status]@row = "Sent to Customer for Approval - Revised Drawing from F1/Addendum - Previously Proposed", "Costumer", IF([Estimate Status]@row = "Revised Drawing from F1/Addendum Approved, NAM to Resubmit Proposal", "Riley", IF([Estimate Status]@row = "Proposal Resubmitted - based on New F1/Addendum Received", "Riley", IF([Estimate Status]@row = "PO Received", "Riley", IF([Estimate Status]@row = "Submitted for Booking", "Riley", IF([Estimate Status]@row = "Booked", "Riley", IF([Estimate Status]@row = "Case Needed for Drawing Verification/Update, New F1/Addendum Issued - Booked Job", "Riley", IF([Estimate Status]@row = "Sent to Customer for Approval - Revised Drawing from F1/Addendum - Booked Job", "Customer", IF([Estimate Status]@row = "ON HOLD Presales", "Riley", IF([Estimate Status]@row = "ON Hold Booked Job", "Rachel", IF([Estimate Status]@row = "Completed", "Rachel", IF([Estimate Status]@row = Installation Completed", "Rachel", IF([Estimate Status]@row = "Closed Job in TIP", "Rachel", IF([Estimate Status]@row = "Cancelled", "cancelled", IF([Estimate Status]@row = "Closed", "Rachel", IF([Estimate Status]@row = "Riley to send ANSC to Andrea", "Riley"))))))))))))))))))))))))))))))))))))))))))))))))))

this is what I tried but always showing unparseable

let me know if I can change something! thanks!

Paul Newcome

My suggestion would be a separate sheet that has the status in one column and the person to assign in another column. Then the formula would be much shorter

=INDEX({Reference Sheet Assigned To}, MATCH([Status Column Name]@row, {Reference Sheet Status}, 0))

After that, any adjustments to who gets assigned to which statuses is much more easily managed by making your changes to the reference sheet instead of trying to edit a monster formula.

AngeloV

Hello Paul Newcome! Thanks for the quick answer!
I have this master tracker where the "estimate or contract" info is available, so the Idea from the pm is to automate the sheet, so when someone update the (estimate status) , automatically the next 2 columns will have to change to a person and a code in the other column thats why they gave me the "IF" formula, but still, I cant make it work in the mastertracker with the suggested formula, still trying, Thank you very much!.

Protonsponge

Hello @AngeloV,

I think @Paul Newcome has given some great guidance here for you that would enable you to modify with ease going forward via a separate sheet.

I did have a look at your formula and I think you are missing a a single " in your IF([Estimate Status]@row = Installation Completed", and a few closing brackets in your formula.

I think the following might work for you however it is quite the nested IF statement. It is worth noting that this formula it is very close to the cell character limit and you will not be able to expand it much more. If you need to build on the formula below, its well worth looking at the option proposed by Paul Newcome.

=IF([Estimate Status]@row = "Case Needed", "Riley", IF([Estimate Status]@row = "Design Underway", "Lester", IF([Estimate Status]@row = "Design Underway - Previously Proposed", "Lester", IF([Estimate Status]@row = "Design Underway - Previously Booked", "Lester", IF([Estimate Status]@row = "Case Needed for Drawing Verification/Update, New F1/Addendum Issued - Previously sent to Customer for Approval", "Riley", IF([Estimate Status]@row = "Case Needed for DD01 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD02 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD03 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD04 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD05 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD06 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD07 Change Request", "Riley", IF([Estimate Status]@row = "Case Needed for DD08 Change Request", "Riley", IF([Estimate Status]@row = "Design Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD01 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD02 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD03 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD04 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD05 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD06 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD07 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "DD08 Sent to Customer for Approval", "Customer", IF([Estimate Status]@row = "Design Approved, NAM to Propose", "Riley", IF([Estimate Status]@row = "DD01 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD02 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD03 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD04 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD05 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD06 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD07 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD08 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD09 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "DD10 Approved NAM to Propose", "Riley", IF([Estimate Status]@row = "CO Approved, NAM to Propose", "Riley", IF([Estimate Status]@row = "Case Needed for Drawing Verification Update New F1 Addendum Issued Previously Proposed", "Riley", IF([Estimate Status]@row = "Proposed", "Customer", IF([Estimate Status]@row = "Sent to Customer for Approval - Revised Drawing from F1/Addendum - Previously Proposed", "Costumer", IF([Estimate Status]@row = "Revised Drawing from F1/Addendum Approved, NAM to Resubmit Proposal", "Riley", IF([Estimate Status]@row = "Proposal Resubmitted - based on New F1/Addendum Received", "Riley", IF([Estimate Status]@row = "PO Received", "Riley", IF([Estimate Status]@row = "Submitted for Booking", "Riley", IF([Estimate Status]@row = "Booked", "Riley", IF([Estimate Status]@row = "Case Needed for Drawing Verification/Update, New F1/Addendum Issued - Booked Job", "Riley", IF([Estimate Status]@row = "Sent to Customer for Approval - Revised Drawing from F1/Addendum - Booked Job", "Customer", IF([Estimate Status]@row = "ON HOLD Presales", "Riley", IF([Estimate Status]@row = "ON Hold Booked Job", "Rachel", IF([Estimate Status]@row = "Completed", "Rachel", IF([Estimate Status]@row = "Installation Completed", "Rachel", IF([Estimate Status]@row = "Closed Job in TIP", "Rachel", IF([Estimate Status]@row = "Cancelled", "cancelled", IF([Estimate Status]@row = "Closed", "Rachel", IF([Estimate Status]@row = "Riley to send ANSC to Andrea", "Riley"))))))))))))))))))))))))))))))))))))))))))))))))))))

I hope that is helpful to you in some way,

Protonsponge.

AngeloV

Hello! Great! I think I made it happen, with a vlookup. the nested IF formula was just too much and you are totally right if they need more things there adding more conditions will just be impossible.