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
-
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.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
-
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")))
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
-
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.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"
-
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.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"
-
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.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!😀
-
You are welcome, I will be happy to help you any time.
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!