While working on a recent project I was creating/updating some site columns on an intranet which was based on Windows SharePoint Services v3.0. The changes were mainly creating new site columns to capture additional data and adjusting some existing columns, including a few calculated columns. I have used calculated columns in other projects so was expect the changes to be very straight forward.
When looking at the formula site columns I copied the existing formula into notepad twice using one to change and one as a backup. I made my changes to the formula and then pasted the code back into SharePoint but when I updated the site column I got the following error:
“The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.”
While I was positive none of my changes could have resulted in this error I went back and compared my new version to the original just in case I had accidentally changed one of the column names but they were all correct. Next I checked that all the columns used in my formula were in the ‘Insert Column’ list to the right hand side of the formula box in case any of the columns had been deleted since the work was originally done but again everything was as it was supposed to be. When I went back over my new formula I noticed that while one of the fields was wrapped in square brackets, [column], another wasn’t. My initial reaction was that shouldn’t make a difference as my understanding was only fields that contained spaces in the name required square brackets but I though I would add them anyway. The result was the update to the formula worked.
I have since done some additional testing and it is true that when you add a new columnn from the ‘Insert Column’ list it automatically adds square brackets around it but if you then click ok and go back into the formula it seems to remove those square brackets from any columns that don’t have spaces in the name. I have spent some time testing on a development environment but can’t seem to establish exactly what it was about this particular formula that required the square brackets around the site column name.
At the end of the day I managed to get the functionality to work but I would still be interested in others experience of anything similar.