Calculated column formula works in Excel but not in SharePoint
-
30 เมษายน 2555 21:26
I have some help creating a calcualted column in the Excel forumns which works great in Excel but not in SP. I guess I shouldn't have assumed it would convert perfectly but it looks like SP doesn't like brackets "{" OR "}".
I have 3 columns ("CY T4", "CY T5", "CY Other"), all can have 1 of 3 different values (Yes, NR, NUL). I want mt calc column to display "Complete" OR "Not Complete" depending on the values of the 3 first columns.
If there is a "Yes" OR "NR" in all 3 columns it is "Complete", otherwise it is "Not Complete"
My Formula =IF(AND(OR(CY T4={"Yes","NR"}),OR(CY T5={"Yes","NR"}),OR(CY Other6={"Yes","NR"})),"Complete","Not Complete")
Can someone tell me if there a way to use this formula in SP or another approach to the same result.Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
ตอบทั้งหมด
-
30 เมษายน 2555 21:54
Some more background.
I have already created a column called "CY Status" (picture) and use conditional formatting to display a graphic that will represent the status of all 3 "CY..." columns but can not group or filter by the CY Status column. I was hoping to some how group and/or filter this view so staff can see items that still require their attention. That is why I am trying the above formula on a calculated column so grouping was possible.
Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
-
1 พฤษภาคม 2555 18:16
I have found a long solution to this issue.
I need a Calc column StatusCounter that will use the following formula.
- =IF([CY T4]="Yes",1,0)+IF([CY T5]="Yes",1,0)+IF([CY Other]="Yes",1,0)+IF([CY T4]="NR",1,0)+IF([CY T5]="NR",1,0)+IF([CY Other]="NR",1,0)
Then I can use another Cacl column (CY Status) to set the Complete/Not Complete by using a =IF([StatusCntr] = 3, "Complete","Not Complete").
Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
- ทำเครื่องหมายเป็นคำตอบโดย Stunpals 1 พฤษภาคม 2555 18:17
-
1 พฤษภาคม 2555 22:58
I wonder if this would work?
=IF(AND(AND(OR(field1="Yes", field1="NR"), OR(field2="Yes", field2="NR")),OR(field3="Yes", field3="NR") ), "Complete", "Not Complete")
Robin
-
2 พฤษภาคม 2555 15:38
Robin,
You are correct this does work as well and is a bit cleaner as far as I can see but I'm not an expert. Since mine is setup already I'll probably leave it but will keep this format in mind as I believe there is a IF limit of 7 in a calculated column. At least that is what I have read.
Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.
.png)
