Share via

the formula refers to a column that does not exist. check the formula for spelling mistakes or change the non existing column for existing column

Question

Thursday, May 10, 2012 10:18 AM

sharepoint error

All replies (3)

Friday, May 11, 2012 3:29 AM âś…Answered

Hi Ruchi,

How did you set the formula in your calculate column ?

Please check whether the brackets are removed in the formula, if removed, re-add them or re-create the calculated column, then check result again,

http://snahta.blogspot.com/2010/05/formula-refers-to-column-that-does-not.html

Thanks

Daniel Yang

TechNet Community Support

Thursday, May 10, 2012 11:20 AM

Hi,

The there are multiple things you need to take care of while creating calculated column, please have a look at this MSDN thread for solution http://social.technet.microsoft.com/Forums/en/sharepointgeneral/thread/f5fde0ac-79da-47a3-a881-1cfcff0dba60, if it dosent solve your problem then please share more details on the issues.

Thanks.... ________________ Baba (MCTS, MCPD)

Tuesday, June 5, 2012 6:48 AM

Hi all...

Like many others, this errror did my head in for a while, but I've worked out a solution that may work for your scenario. There appears to be a bug in SharePoint 2010. It causes this error when updates are made to calculated site columns that reference other columns and are already in use in lists or libraries. What makes this more difficult to debug is that it doesn't occur in most of the time, but when it does it consistently errors.

Scenario

Say we have:

  • A single line text site column "Text X"
  • A calculated site column "Calc Y". with a formula: =IF([Text X]="Foo", "Yes", "No").
  • Both are attached to Site Content Type "My Sample Type"
  • "My Sample Type" is then attached to a List "My Sample List"

If the error presents itself, it will occur if you update the site column "Calc Y". In this case it will throw a Correlation ID with the description: The formula refers to a column that does not exist.

This also occurs if you try the same thing in PowerShell:

$MyWeb.Fields["Calc Y"].Update($True)

The Apparent Cause

It seems to occur because of the way updates are propogated from the Site Column to the List Column. Hopefully a dev on the MS SP2010 team can enlighten us here...

The Solution

Simply stagger the updates. Update the "Calc Y" column first without updating lists that use it (there is a radio button in the UI). Then repeat the update, but this time update the lists that use it. This can also be achieved in PowerShell with:

$MyWeb.Fields["Calc Y"].Update($False)
$MyWeb.Fields["Calc Y"].Update($True)

You may also get this error thrown when deploying New sites using custom templates that contain your calculated column and list. In this case you can template the site with a dummy formula "=1" (ie not referencing another column), then use PowerShell to update the formula, post creation. This would be done with:

$MyWeb.Fields["Calc Y"].Formula = "=IF([Text X]="Foo", "Yes", "No")"
$MyWeb.Fields["Calc Y"].Update($False)
$MyWeb.Fields["Calc Y"].Update($True)

Hope this helps :)