or Explore Discussions

# same formula is not working in different columns

07/28/21
Accepted

Hi Experts,

I use the same formular on different columns of my sheet. BUT in one column I get the wrong results.

Here works perfectly and shows the red, green, yellow dots when it's needed,

=IF([Dev sample needed?]@row = "", "", IF(OR([Planned ex-factory date]@row = "", [Actual ex-factory date]@row = ""), "Yellow", IF([Actual ex-factory date]@row < DATE(2021, 11, 2), "Green", "Red")))

Here it shows the correct results only in the first cell of the column. On the other cells it show a yellow, red or green dot also when the [Dev sample needed?] is not empty (flagged).

=IF([AC-T sample needed?]@row = "", "", IF(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "Yellow", IF([Actual ex-factory date AC-T samples]@row < DATE(2021, 11, 2), "Green", "Red")))

What cause the issue?

Thanks

Christine

The following formula after i added a condition for the Red ball and show Empty if Flag is not checked.

```=IF(AND(OR([Planned ex-factory date]@row = "", [Actual ex-factory date]@row = ""),
[Dev sample needed?]@row = 1), "Yellow", IF(AND([Actual ex-factory date]@row
< DATE(2021, 11, 2),[Dev sample needed?]@row = 1), "Green",
IF(AND([Dev sample needed?]@row = 0,[Actual ex-factory date]@row = ""),"",
IF(AND([Dev sample needed?]@row = 1,[Actual ex-factory date]@row <> "",
[Planned ex-factory date]@row <> "",[Actual ex-factory date]@row >= DATE(2021, 11, 2)),
"Red"))))
```

PMP Certified

[email protected]

www.mobilproject.it

• Hi @Christine Menke

Hope you are fine, please try the following formula:

```=IF(ISBLANK([AC-T sample needed?]@row),"",
IF(OR(ISBLANK([Planned ex-factory date AC-T samples]@row) ,
ISBLANK([Actual ex-factory date AC-T samples]@row), "Yellow",
IF([Actual ex-factory date AC-T samples]@row < DATE(2021, 11, 2), "Green", "Red")))
```

PMP Certified

[email protected]

www.mobilproject.it

• Thanks @Bassam Khalil

it seems not to work.

I get an error : Incorrect Argument Set

I have updated it to this :

=IF(ISBLANK([AC-T sample needed?]@row), "", IF(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "Yellow", IF([Actual ex-factory date AC-T samples]@row < DATE(2021, 11, 2), "Green", "Red")))

and it still shows yellow, red or green dot also when the [Dev sample needed?] is not blank(flagged).

Thanks

• If you can share me as an admin on a copy of your sheet  (after removing or replacing any sensitive information) i will try to fix it for you.

PMP Certified

[email protected]

www.mobilproject.it

• i fixed your formula also the formula in the other column that you said it's correct i fix it for you because i found one error. please check it.

```=IF([AC-T sample needed?]@row = 0, "", IF(OR([Planned ex-factory date AC-T samples]@row = "",
[Actual ex-factory date AC-T samples]@row = ""), "Yellow",
IF([Actual ex-factory date AC-T samples]@row < DATE(2021, 11, 2), "Green", "Red")))
```

PMP Certified

[email protected]

www.mobilproject.it

The following formula after i added a condition for the Red ball and show Empty if Flag is not checked.

```=IF(AND(OR([Planned ex-factory date]@row = "", [Actual ex-factory date]@row = ""),
[Dev sample needed?]@row = 1), "Yellow", IF(AND([Actual ex-factory date]@row
< DATE(2021, 11, 2),[Dev sample needed?]@row = 1), "Green",
IF(AND([Dev sample needed?]@row = 0,[Actual ex-factory date]@row = ""),"",
IF(AND([Dev sample needed?]@row = 1,[Actual ex-factory date]@row <> "",
[Planned ex-factory date]@row <> "",[Actual ex-factory date]@row >= DATE(2021, 11, 2)),
"Red"))))
```

PMP Certified

[email protected]

www.mobilproject.it

• Thanks a lot for your help Bassam Khalil ! It works with the latest formula!😀

• You are welcome, I will be happy to help you any time.

PMP Certified

[email protected]

www.mobilproject.it