Using IF Function - Multiple Scenarios and Multiple resulting values

mabedelfatah

I'm trying to establish formulas that will help display turnaround times. This is really hard for me to articulate but I'll try.

Column/Label #1 is named "Service Request Type" and the column/label I want populated with a formula is named "Target" and the turnaround times to use to populate the "Target" column is in another Smartsheet named "Turnaround Times". But then I also need to incorporate that if another value then the turnaround is different.

SCENARIO: If "Service Request Type" = "FUNDING BOOKING - NEW LOAN PROCEEDS", OR = "HUD/CLOSING STATEMENT", OR = DOC PREP, then reference "Turnaround Times" Smartsheet and select a specific static cell to populate "Target" column with that value; OR If "Service Request Type" = "Attorney Doc Review", OR = "SBA BOOKING", then reference "Turnaround Times" Smartsheet and select a specific static cell to populate "Target" column with that value.

The following single formula works well if I select only one choice from "Service Request Type". =IF([SERVICE REQUEST TYPE]@row = "FUNDING / BOOKING - NEW LOAN PROCEEDS", {Turn Around Times Range 3})

I don't know how to combine the multiple choices. Please can someone help?

Kelly Moore

Hey @mabedelfatah

You are wanting to incorporate nested IF/OR functions. Usually with cross sheet references (different sheets) we have to employ a slightly syntax approach in writing any kind of IF statement. However, since you are only referring to single static cells, the 'same sheet' IF syntax will work.

=IF(OR([SERVICE REQUEST TYPE]@row = "FUNDING BOOKING - NEW LOAN PROCEEDS", [SERVICE REQUEST TYPE]@row = "DOC PREP"), {Turn Around Times Range 3}, IF(OR([SERVICE REQUEST TYPE]@row = "Attorney Doc Review", [SERVICE REQUEST TYPE]@row = "SBA BOOKING"), {Turn Around Times Target 2}))

Don't forget that you must physically insert the reference into your sheet from the formula dialog box - you cannot simply copy paste the formula into your sheet.

Does this work for you?

Kelly