I ran into this today and thought it might be something worth jotting down to save others the same pain.
When you’re editing a list in Datasheet view in SharePoint 2010 and you’re working with a Calculated column, you can see the formula at the bottom of the view for the cell in which you have your cursor. All well and good. See the screen snip below; the yellow highlighting is mine.
Since it’s a Datasheet view, however, it’s pretty easy to mistakenly type a number into the cell without noticing that it’s a Calculated column. When you do this, you get the prompt below. Again, my highlighting.
Now, here’s where I messed myself up. I didn’t read the message carefully enough and just thought it was telling me that it would take a while to be helpful. In fact, what was happening was that SharePoint was about to replace the formula in the column with a new formula, =50. This meant that every cell then had the value 50 rather than the previous calculation.
It was easy enough to fix, once I figured out what I had done, but some calculations I was doing in a DVWP sure looked crazy for a bit!
I rarely remember this, and virtually no one else knows, either. If you’re editing the contents of a SharePoint list in Datasheet view, entering the wrong data can be disastrous. Never use Datasheet view unless you really know what you are doing, as the changes are destructive, meaning that there’s rarely a way to undo or repair your changes. (Beware ye who enter here: There be dragons!)
But there is an undo, actually. Bet you can’t find it if you look for it, either! See that little bar I’ve highlighted below? If you click on it, you’ll get various options which you can take based on the list and what software you have installed on your machine.
In the image below it’s expanded, and you can see the undo icon. It’s not highlighted here because I haven’t made a change.
The undo here is even a multi-action undo, like you’re used to in other Microsoft Office applications. Just be careful; it won’t undo all types of changes, and as soon as you commit your changes, you’re out of luck.
I recently updated my Windows XP SP3 machine with some recommended updates from Windows Update. After I did so, I received the following error every time I tried to open a Datasheet view in SharePoint:
The list cannot be displayed in Datasheet view for one or more of the following reasons: A datasheet component compatible with Windows SharePoint Services is not installed, your browser does not support ActiveX controls, or support for ActiveX controls is disabled.
Poking around on the Web, I found many solutions for folks who were using Office 2003. Here’s the most common solution I found:
- Close Internet Explorer
- Go to Start –> Run… –> “regedit”
- Delete the following folder in the registry:
Path: HKEY_LOCAL_MACHINESOFTWAREMicrosoftInternet ExplorerActiveX Compatibility
Unfortunately, I’m running Office 2007 and that registry key isn’t in the registry on my machine. However, I do happen to have Access 2003 still loaded on the machine (my Office 2007 install didn’t include Access), so I tried to do a Detect and Repair in Access 2003 to see if that would solve the problem. Nope.
I had one more idea: I ran Office Diagnostics in Outlook 2007 (any Office 2007 application has this option under Help or application options). I closed all of my Internet Explorer windows and tried again. Success!