Experienced in both Excel 2010 and Excel 2013.

So I've run in to this behaviour before but decided to open a quick discussion about it.

So take the following scenario, I've created a spreadsheet where a non-technical person will be performing some simple data entry.

I've configured any cells which perform some sort of calculation so they are Locked (in the example below these cells are the shaded cells), and then protected the sheet allowing only the default actions (essentially data entry only on unprotected cells, no formatting changes, etc).

This works as expected, users can enter data in the unlocked cells and cannot change any formula / data in the locked cells. The problem comes when a user has entered some figures in the wrong cells and cuts and pastes the data elsewhere in the sheet. Excel changes the references in the formula in the locked cells, subsequently breaking any calculations and the user has no idea what's going on!

It's also worth noting that it also moves the formatting as well, despite the sheet protection's intention to prohibit formatting changes.

This can be demonstrated in the video:

<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/h-92adimlWM" width="560"></iframe>

https://www.youtube.com/embed/h-92adimlWM

This seems to undermine the whole point of being able to protect worksheets! This is the behaviour I would expect from an unprotected sheet, but not when I've locked down the spreadsheet to prevent exactly these kinds of alterations.

I'll also note that copying and pasting data does not (for obvious reasons) update any formula references but will make formatting alterations to the sheet.

Has anybody else had experience with this? Am I just expecting too much of Excel ?!

Thanks,

Chris

• Edited by chrislongridge Friday, May 29, 2015 10:35 AM Added Excel versions.

Hi Chris,

Based on your description, you wonder to configure a workbook with protected setting specify the users where can enter the data but not protect the whole worksheet. Is my understanding right? But now the problem is the user enter data in the wrong cells then cut and pasted in other cell, this behavior broke the formula in the cell the data or formula didnt get the correct result.

I suppose if your issue might be caused by absolute reference? For example, if the formula with absolute reference when you move the cells to other cells, you will find the reference of cells dont change. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant. Based on the video I suppose your issue might be caused by using absolute reference in your formula, so you get the #REF! Error. An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both. You can check it by yourself.

I suggest you upload a sample via OneDrive so that I can help you check if the issue is caused by absolute reference.

Hope it's helpful.

George Zhao
TechNet Community Support