0

I had a formula in a calculated column that worked just fine. I tried to revised the formula, reusing part of what worked before (in bold), but it keeps taking me to syntax error screen. The new formula is much simpler.

I've looked online, but can't find the issue.

--Old formula (works):

=IF(AND(LEFT([EVENT TYPE],8)<>"Mtng (PM",LEFT([EVENT TYPE],8)<>"Mtng (eP",LEFT([EVENT TYPE],8)<>"Mtng (DS",LEFT([EVENT TYPE],8)<>"Mtng (#D"),"N/A for this row", IF(OR([(DC1) EDS?]="",[(DC2) Part Info]="",[(DC3) Part Innov]="",[(DC4) Other]=""),"Fill DC columns (1-4)",IF(OR(LEFT([(DC1) EDS?],1)<>"4",LEFT([(DC2) Part Info],1)<>"4",LEFT([(DC3) Part Innov],1)<>"4",LEFT([(DC4) Other],1)<>"4"),"Yes","No")))

--New Formula (throws error) - bold portion copied entirely from old formula:

=IF(AND(LEFT([EVENT TYPE],8)<>"Mtng (PM",LEFT([EVENT TYPE],8)<>"Mtng (eP",LEFT([EVENT TYPE],8)<>"Mtng (DS",LEFT([EVENT TYPE],8)<>"Mtng (#D"), "N/A for this row", [Add to Data Collection Calendar?])

The syntax and condition in the revised formula (in bold) is straight out of the old formula. Instead of adding other If statements, I just want the formula to enter the contents of "Add to" column if condition is false.

The "Add to..." column is a choice column.
The "EVENT TYPE" column referred in condition is also a choice column. (This condition evaluates correctly in the old, longer formula).

I've tried to set the calculated column to simply "=[Add to Data Collection Calendar?]" to check if there's an issue with "Add to..." column, but that formula works and gets me the contents of "Add to..." column.

It's got to be something simple that I can't seem to find.

UPDATE (6/3/19) I entered the new formula in Excel, adding two columns for [EVENT TYPE] and [Add to Data Collection Calendar?] and the formula syntax works fine giving accurate output: 

=IF(AND(LEFT(A4,8)<>"Mtng (PM",LEFT(A4,8)<>"Mtng (eP",LEFT(A4,8)<>"Mtng (DS",LEFT(A4,8)<>"Mtng (#D"), "N/A for this row", C4)

I didn't think I needed to since old formula worked, but still tested setting formula to "=[EVENT TYPE]" and getting syntax error! So the issue is with EVENT TYPE column. Why would the same column give error now when it worked fine in previous formula? (EVENT TYPE is a choice column with dropdown for single selection).

Qudsia
  • 53
  • 8

1 Answers1

0

Solved: This is bizarre on more than one level.

I found a solution from a post by someone whose column was named exactly the same "EVENT TYPE". The solution offered by someone (link below) was to remove the space between the two words to make it "EventType". I added an underscore instead, and IT WORKED!

The strange part is, the other column in the formula "Add to Data...." works fine with all the spaces. The square brackets are suppose to take care of spaces in any case.

Perhaps "EVENT TYPE" has keyword/s that triggered the issue. But again, they worked fine in previous formula. Perhaps simply changing the column name corrected for something, like rebooting works for some Excel issues?!

Solved by using this link: https://sharepoint.stackexchange.com/questions/86548/unable-to-add-a-choice-field-to-a-calculated-field-sharepoint-foundation-2013

Qudsia
  • 53
  • 8