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

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.