Formula refers to a column that does not exist

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.

Advertisements

One Response to Formula refers to a column that does not exist

  1. Isha says:

    I know this is an old post but I wanted to add my experience. I received this error after making some changes to a site column of the calculated column type. In my case, the site column is from a site called “Projects” and I have a template called “Blank Project” which is rolled out as projects come along. All sorts of based data/lists/libraries/workflows are in this template. Each project is created under a site called “Projects.”

    “Blank Project” and thus every single sub site uses the site columns from “Projects.” I made the changes, packaged the “Blank Project” site as an updated template and tried a test roll out. No go. I got the “Formula refers to blah blah…” error.

    After taking a look at the SP logs and tracking the correlation ID, I figured out which list it was (I had made many changes to the template) I checked spelling, brackets, formulas. Pulled my hair out because this error is incredibly annoying. It will NOT pin point which field is causing the issue! Then I recalled that in early development we had used the “Projects” site as a template for a sibling site called “Special Projects”. Despite being separate “parallel” sites I have found that the site columns are 100% linked. However, my changes to the formula in the “Projects” site did not reflect in the “Special Projects” site column.

    I supposed that because they are not parent/child sites a sort of “orphaned” site column was created? I’m really still trying to figure out what is going on, but in short, I had to manually update the “orphaned” site column in the “Special Projects” site to be exactly like the column in “Projects” before I could roll out my “Blank Project” template. Hope that makes sense and helps someone else.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: