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

Meghna Mudhol

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

Meghna Mudhol

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

Brian_Richardson

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