same formula is not working in different columns

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

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/29/21 Answer ✓

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/28/21

    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

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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
    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    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")))
    

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/29/21 Answer ✓

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Christine Menke

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!