same formula is not working in different columns

Christine Menke

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

Bassam Khalil

@Christine Menke 

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

Bassam Khalil

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

Christine Menke

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

Bassam Khalil

@Christine Menke 

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.

Bassam Khalil

@Christine Menke 

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

Bassam Khalil

@Christine Menke 

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

Christine Menke

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

Bassam Khalil

@Christine Menke

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