[This post was originally published (by me) on the SharePoint365 blog (http://sp365.co.uk)%5D

This issue arose several times in my company, so I imagine others have also run into it… [Note: this article applies to both MOSS and SharePoint 2010]

Here’s the (simplified) scenario: I have a custom list called Jobs, containing basic information about the jobs for which my company is supplying stairs.

One of the things we want to make available to employees via SharePoint is the job’s profit to date. We’re using a calculated column to arrive at that figure, using this formula:

Each Monday, we want a clerical person on our staff to update the Cost to Date and % Complete figures based on the previous week’s production. (Of course, ultimately it would be best to have this updated automatically from external data, but for now let’s say this is a manual process.) To make this weekly update simpler for our clerk, we’ve shown them how to change to Datasheet View, so they can easily tab from one cell to the next, entering their updates.

So one fine Monday, our clerk is merrily updating costs and percentages, and they accidentally tab over to the Profit to Date column and update that as well…

A message will pop up telling the user to preview the results of their calculation change, but your average user will likely not realize the implications of this message and will click Continue as a matter of course.

And then…

Your clerk will see (if they notice at all) that the Profit figure on EVERY item has changed to the figure they entered for the one item. Or possibly (as happened to us) someone else will notice it the next time there’s a staff meeting and you’re looking at profitability…

But…

All you need to do to fix this is to go back under List Settings and re-enter the Profit to Date calculation…

You’ll see when you edit the Profit to Date column that what has happened is that the formula changed to equal the figure that was entered into the column from the Datasheet view. So you just need to override that with your previous formula.

You DID document that complex formula you wrote, didn’t you…? 🙂

It would be nice if you could tell SharePoint to protect as Read-only certain columns, as you can in Excel, but I haven’t found any information that says you can do this (someone please correct me if I’m wrong…).

Another solution would be to create a separate data entry View for your staff to use for updating, which does not include any calculated columns. However, I do think it’s helpful to include dependent columns on your “updating” view, so that the user can see what effect their entries have on the dependent column (as a reasonableness check on what they’ve entered).

Thus, part of user training on this particular type of process needs to be to tell them to watch out for that “preview calculation change” message, and DO NOT click Continue if they see it.

I recall that the first time this happened at my company, I just about had a heart attack until I figured out what had happened and how to fix it (and yes, it still did happen more than once). Hopefully this post will help you either avoid this situation, or if it does happen, at least you’ll know not to panic… 🙂