Formula Question

HZAR

Hi

I have the formula below that indicate the close, denied, cancel and open status which is currently working

=IF([GSP Status]@row = "Cancel", "CANCEL", IF([GSP Status]@row = "Denied", "DENIED", IF(NOT(ISBLANK([Final Settlement PO ($USD)]@row)), "CLOSED", IF([Final Settlement PO ($USD)]@row = "", "OPEN"))))

Right now if I want to change the formula as scenario below

Status: GSP status=Cancel, Status=Cancel

Status:GSP status=Denied, Status=Denied

Final settlement PO is not blank=Closed

If "Current Queue" = "Claim Approved" or "Claim & Reserves Approved" & Final Settlement PO ($USD) is blank, then mark as "Open - Approved"

If "Current Queue" is not "Claim Approved" or "Claim & Reserves Approved" & Final Settlement PO ($USD) is blank, then mark as "Open - WIP"

Anyone can advise how I can twit formula to follow the scenario above. Thanks

Paul Newcome

Give this a try:

=IF([GSP Status]@row = "Cancel", "CANCEL", IF([GSP Status]@row = "Denied", "DENIED", IF([Final Settlement PO ($USD)]@row <> "", "CLOSED", IF(OR([Current Queue]@row = "Claim Approved", [Current Queue]@row = "Claim & Reserves Approved"), "Open - Approved", "Open - WIP"))))

Paul Newcome

@HZAR Try this:

=IF(OR(AND([Approval Required]@row = "1", [Type]@row = "Non MP"), [Type]@row = "MP"), VLOOKUP(Commodity@row, {Buyer Range 1}, 5, 0))

Itai Perez

Hi @HZAR

Can you share a screenshot to help visualize the issue?

Thanks

Paul Newcome

Give this a try:

=IF([GSP Status]@row = "Cancel", "CANCEL", IF([GSP Status]@row = "Denied", "DENIED", IF([Final Settlement PO ($USD)]@row <> "", "CLOSED", IF(OR([Current Queue]@row = "Claim Approved", [Current Queue]@row = "Claim & Reserves Approved"), "Open - Approved", "Open - WIP"))))

HZAR

Hi @Paul Newcome

Sorry for any late update. Thanks for the formula. It works as per required output. It is awesome

HZAR

Hi @Paul Newcome

I have a question about my formula below which is giving me unparseable error. Is my formula wrong? If yes, can you show me the correct way. Thanks

=IF([Approval Required]@row = "1", [Type]@row = "Non MP"), IF(OR([Type]@row = "MP"), VLOOKUP(Commodity@row, {Buyer Range 1}, 5, 0)

The scenario that i would like to have

If Type is Non MP and approval required, then vlook buyer name

If Type is MP-vlook buyer name

Paul Newcome

@HZAR Try this:

=IF(OR(AND([Approval Required]@row = "1", [Type]@row = "Non MP"), [Type]@row = "MP"), VLOOKUP(Commodity@row, {Buyer Range 1}, 5, 0))

HZAR

Hi @Paul Newcome

Thanks for the guidance as always ;)

Paul Newcome

Happy to help. 👍️

HZAR

@Paul Newcome

I have a formula below but it giving me the error. Not sure what is causing the issue. Need your advise and help

=IF(OR([Item Status]@row ="Life Time Buy (LTB)", [Item Status]@row ="Bridge Buy (BB)"),"adrienanurashyiqin.bintizainol@gmail.com", IF([GSP Status]@row = "Cancel", "", IFERROR(INDEX({Site Ivt Lead}, MATCH([EMS Site]@row, {Site1}, 0)), "")))

The flow will be

Item Status=LTB or BB , EMS Site-Any-Lead name will be adreina

Items Status is not under LTB or BB, It will Vlook Lead name based on the EMS site

If GSP status =Cancel, Lead name will be blank

Paul Newcome

Which error exactly are you getting?

HZAR

Hi @Paul Newcome

I get the #UNPARSEABLE error for this case. Thanks

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

HZAR

Hi @Paul Newcome

I have attached the screenshot. Thanks

Original formula will bee as below

IF([GSP Status]@row = "Cancel", "", IFERROR(INDEX({Site Ivt Lead}, MATCH([EMS Site]@row, {Site1}, 0)), ""))

As per the flow that is required I have amend the formula as below which is currently giving me the error

=IF(OR([Item Status]@row ="Life Time Buy (LTB)", [Item Status]@row ="Bridge Buy (BB)"),"adrienanurashyiqin.bintizainol@motorolasolutions.com", IF([GSP Status]@row = "Cancel", "", IFERROR(INDEX({Site Ivt Lead}, MATCH([EMS Site]@row, {Site1}, 0)), "")))

The flow will be

Item Status=LTB or BB , EMS Site-Any-Lead name will be adreina

Items Status is not under LTB or BB, It will Vlook Lead name based on the EMS site

If GSP status =Cancel, Lead name will be blank

Item Status column

Reference Column-Have link the Site with Name

Paul Newcome

I would need to see the formula that is throwing the error, and I would need to see it open in the sheet as if you are about to edit the formula similar to the snippet below:

HZAR

Hi @Paul Newcome

I think the screenshot is not related to my ask I guess. Any update on my question. Thanks

Paul Newcome

The screenshot could show other pieces that are being missed based on the default color coding and whatnot. There has been nothing that you have provided thus far that would indicate why you are getting that error. Without the additional details, I have helped as much as I can.