Help on my formula please
Hi all ,
Formula not working as shown be;ow , can you help with what i have done wrong please? Thanks
=IF([Quote SLA For Remedial]@row, <>"", "Awaiting Quote", IF([Remedial/CAFM Job Number]@row, <>"", "Yes", IF([Remedial/CAFM Job Number]@row, "", "No")))
Best Answer

If the date in Quote SLA for Remedial is driven by a formula then I am not sure if you need it including or not as the yes/no looks to be based on if there is a job number and 1 of 2 status being in "Remedial Status".
If excluding, does something like this work?
=IF(AND([Remedial/CAFM Job Number]@row <> "", OR([Remedial Status]@row = "Remedial Required", [Remedial Status]@row = "Overdue Remedial")), "Yes", IF(AND([Remedial/CAFM Job Number]@row = "", OR([Remedial Status]@row = "Remedial Required", [Remedial Status]@row = "Overdue Remedial")), "No", IF([Remedial Status]@row = "No Remedial", "")))
Sample result:
If the "Quote SLA for Remedial" is required in the formula (for example you want to compare if the date there is within a number of days to today) then you can add this into AND portion of the formula, but I'm not entirely sure if that's required.
Does this help, or have I misunderstood something?
Answers

Hi @Charlie H ,
You have some extra commas in your formula which are what is messing things up.
It should be along the lines of:
=IF([Quote SLA For Remedial]@row <>"", "Awaiting Quote", IF([Remedial/CAFM Job Number]@row <>"", "Yes", IF([Remedial/CAFM Job Number]@row = "", "No")))
However, if you still have a value in Quote SLA for Remedial but have a Remedial/CAFM Job Number it will still give an "Awaiting Quote" response, so you may wish to adjust to this:
=IF([Remedial/CAFM Job Number]@row <> "", "Yes", IF([Quote SLA For Remedial]@row <> "", "Awaiting Quote", IF([Remedial/CAFM Job Number]@row = "", "No")))
Hope this helps, but let us know if there are any more problems/questions on this!

Hi Nick ,
It works but its not exactly how i want it. let me explain and hopefully it makes sense mate. See snippet below of the columns.
I only want a date in the 'Quote SLA For Remedial' column if the 'Remedial Status' Column is either 'Remedial Required' or 'Overdue Remedial'. if then the 'Quote SLA For Remedial' column has a date in (this is because the 'Remedial Status' column is either "Remedial Required" or "Overdue Remedial") , but the 'Remedial/CAFM Job Number' is blank then i want it to show as "No". If the 'Remedial/CAFM Job Number' isn't blank then i want it show as "Yes".
But if the 'Quote SLA For Remedial' column is blank (This will be because the 'Remedial Status' column shows as "No Remedial"), Then i also want the 'Quote received in SLA time to also be blank.
I only want the criteria to meet IF the 'Remedial Status' column is either "Remedial Required" or "Overdue Remedial".
Thank you so much for your help so far Nick , i really appreciate it and hopefully this makes sense :)

Any ideas mate? I still cannot crack it to show what I need.
thank you

If the date in Quote SLA for Remedial is driven by a formula then I am not sure if you need it including or not as the yes/no looks to be based on if there is a job number and 1 of 2 status being in "Remedial Status".
If excluding, does something like this work?
=IF(AND([Remedial/CAFM Job Number]@row <> "", OR([Remedial Status]@row = "Remedial Required", [Remedial Status]@row = "Overdue Remedial")), "Yes", IF(AND([Remedial/CAFM Job Number]@row = "", OR([Remedial Status]@row = "Remedial Required", [Remedial Status]@row = "Overdue Remedial")), "No", IF([Remedial Status]@row = "No Remedial", "")))
Sample result:
If the "Quote SLA for Remedial" is required in the formula (for example you want to compare if the date there is within a number of days to today) then you can add this into AND portion of the formula, but I'm not entirely sure if that's required.
Does this help, or have I misunderstood something?

Thanks so much for this , i actually managed to do it with the below formula. I also got a circular reference error which is in fact using the same column name as the cell i am entering the formula in. I took that out and the below worked.
Thank you very much for the help here and commitment :)
=IF(OR([Remedial Status]@row = "Remedial Required", [Remedial Status]@row = "Overdue Remedial"), IF(ISBLANK([Remedial/CAFM Job Number]@row), "No", IF(ISBLANK([Quote SLA For Remedial]@row), "", IF(ISBLANK([Quote SLA For Remedial]@row), "", IF([Remedial/CAFM Job Number]@row <> "", "Yes", "")))), "")
Help Article Resources
Categories
Check out the Formula Handbook template!