IF OR help needed

Options

How can add/fix this formula to leave TD_Field Supervisor as Wes and only change to Andy if someone other than Wes if criteria is true.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 10/30/23 Answer ✓
    Options

    So, you need a formula to show "Andy" if

    TD_Bore is E Bore

    OR

    TD_Ug Construction is Ug Construction

    AND

    TD_Field Sup is not Wes

    Otherwise it shows the name in TD_Feld Supervisor?

    If so, try this formula

    =IF([TD_Feld Supervisor]@row = "Wes", IF(OR([TD_Bore]@row = "E Bore", [TD_Ug Construction]@row = "E Ug Construction"), "Andy", [TD_Feld Supervisor]@row), [TD_Feld Supervisor]@row)

    This first looks to see if the TD_ Field Supervisor is Wes.

    If it is, it uses the original formula you had (shown in bold) to check if TD_Bore is E Bore or TD_Ug Construction if E Ug Construction. If either of those things are true it puts in Andy, if not it puts the name in [TD_Feld Supervisor]@row (which is always Wes).

    Then the last part is what happens if TD_Field Supervisor is not Wes - it just puts in the original name.

    Here it is in context:

    Row 1 is Wes but does not meet the criteria to put in Andy so puts Wes

    Row 2 is Wes and does meet the criteria to put Andy (TD_Ug Construction is E Ug Construction)

    Row 3 is Wes and does meet the criteria to put Andy (TD_Bore is E Bore)

    Row 4 is Wes and does meet the criteria to put Andy (both)

    Row 5 onwards have the same TD_Ug Construction and TD_Bore values are above but the TD_Field Supervisor is Andy those criteria are ignored.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    That formula works. TD Field Supervisor is Andy if TD_Bore is E Bore or if TD_Ug Construction is E Ug Construction. In all other cases it pulls the name from TD_Field Supervisor.

    If you want to hard code Wes and not use the first column, change the green part of the formula, as follows

    =IF(OR([TD_Bore]@row = "E Bore", [TD_Ug Construction]@row = "E Ug Construction"), "Andy", "Wes")

    I'm not sure I understand the question though.

  • mromaire
    Options

    I need it to show Andy if it is "E Bore" or "Ug Construction" is in the cell when the Field Sup is not Wes. There are around 4000 rows with several other Field Supervisors that don't have Bore or Ug Construction criteria in the cells. They are just blank cells. I want the formula to be a column formula. Sorry if I didn't clarify that better before. A little tricky to explain I guess.

  • KPH
    KPH ✭✭✭✭✭✭
    edited 10/30/23 Answer ✓
    Options

    So, you need a formula to show "Andy" if

    TD_Bore is E Bore

    OR

    TD_Ug Construction is Ug Construction

    AND

    TD_Field Sup is not Wes

    Otherwise it shows the name in TD_Feld Supervisor?

    If so, try this formula

    =IF([TD_Feld Supervisor]@row = "Wes", IF(OR([TD_Bore]@row = "E Bore", [TD_Ug Construction]@row = "E Ug Construction"), "Andy", [TD_Feld Supervisor]@row), [TD_Feld Supervisor]@row)

    This first looks to see if the TD_ Field Supervisor is Wes.

    If it is, it uses the original formula you had (shown in bold) to check if TD_Bore is E Bore or TD_Ug Construction if E Ug Construction. If either of those things are true it puts in Andy, if not it puts the name in [TD_Feld Supervisor]@row (which is always Wes).

    Then the last part is what happens if TD_Field Supervisor is not Wes - it just puts in the original name.

    Here it is in context:

    Row 1 is Wes but does not meet the criteria to put in Andy so puts Wes

    Row 2 is Wes and does meet the criteria to put Andy (TD_Ug Construction is E Ug Construction)

    Row 3 is Wes and does meet the criteria to put Andy (TD_Bore is E Bore)

    Row 4 is Wes and does meet the criteria to put Andy (both)

    Row 5 onwards have the same TD_Ug Construction and TD_Bore values are above but the TD_Field Supervisor is Andy those criteria are ignored.

  • mromaire
    Options

    Awesome thanks for the help!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Glad to have helped @mromaire !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!