hi team im trying to combine 2 columns formula in one to get the desired result

Options

Hi team im trying to combine 2 formula in one to get the desired result

=IF(AND([Global Procurement OKR Initiative?]@row = "Global Procurement OKR Initiative 2024", NOT(ISBLANK([GP OKR Initiative 2022]@row))), [GP OKR Initiative 2022]@row, IF(AND([Global Procurement OKR Initiative?]@row = "Global Procurement OKR Initiative 2024", NOT(ISBLANK([GP OKR Initiative 2023]@row))), [GP OKR Initiative 2023]@row, IF(AND([Global Procurement OKR Initiative?]@row = "Global Procurement OKR Initiative 2024", NOT(ISBLANK([GP OKR Initiative 2024]@row))), [GP OKR Initiative 2024]@row, "")))

=IF(AND([CC Live Year]@row = "2023", [Global Procurement OKR Initiative?]@row = "Personal OKR Initiative 2024"), [Project Name]@row, IF(AND([CC Live Year]@row = "2023", [Global Procurement OKR Initiative?]@row = ""), [Project Name]@row, ""))

Answers

  • Meghna Mudhol
    Options

    i need or function if i select any one of them to us

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    You can use OR() similarly to AND(). I'm guessing you're trying to consolidate these formulas to be a little easier to read and follow?

    It looks like your first formulas checks for Global Procurement OKR Initiative? to equal "Global Procurement OKR Initiative 2024", and then to return a value based whether the other yearly initiative columns are blank. So you can consolidate that to just an initial IF statement with follow-on IF statements for the individual years. Because each condition returns a different value, you still need nested IF statements to get to the value you want.

    You can then merge the second formula into the first by continuing the IF chain. In the second formula you are checking for a couple of different conditions and then returning the same value. Because you're returning the same value, this is where you can use an OR().

    So the structure of the formula below is basically:

    • IF Global Procurement OKR Initiative? = "Global Procurement OKR Initiative 2024", THEN
      • IF 2022 isn't blank, set to 2022 value ELSE
      • IF 2023 isn't blank, set to 2023 value ELSE
      • IF 2024 isn't blank, set to 2024 value
    • ELSE, IF CC Live Year = "2023" AND
      • Global Procurement OKR Initiative? = "Personal OKR Initiative"
      • OR Global Procurement OKR Initiative? is blank
      • THEN set to Project Name
    • ELSE blank

    Here's the formula

    =IF([Global Procurement OKR Initiative?]@row = "Global Procurement OKR Initiative 2024", IF(NOT(ISBLANK([GP OKR Initiative 2022]@row)), [GP OKR Initiative 2022]@row, IF(NOT(ISBLANK([GP OKR Initiative 2023]@row)), [GP OKR Initiative 2023]@row, IF(NOT(ISBLANK([GP OKR Initiative 2024]@row)), [GP OKR Initiative 2024]@row))), IF(AND([CC Live Year]@row = "2023", OR([Global Procurement OKR Initiative?]@row = "Personal OKR Initiative", ISBLANK([Global Procurement OKR Initiative?]@row))), [Project Name]@row, ""))

    I've shared a sheet where I used this formula and added the columns referenced, so you can play with the combinations.

    https://app.smartsheet.com/b/publish?EQBCT=58fa06f1007a4ba0a72addd9fbd10aac

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!