Help on my formula please

Charlie H

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

Nick Korna

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?

Nick Korna

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!

Charlie H

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

Charlie H

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

thank you

Nick Korna

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?

Charlie H

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