Forum Discussion
why is excel changing a formula in a locked and protected cell
I have quite a complex spreadsheet for use in my hobby of PBM - the spreadsheet includes an order form for players to submit instructions but with 1 player i am experiencing problems that I cannot fathom why it is happening
Upon emailing my spreadsheet, one of the cells in a hidden and protected part of the form keeps changing, the formula changes for no obvious reason creating an error as it is then trying to bring in/read data from an invalid cell. This only happens with this 1 person, everyone else who receives the exact same spreadsheet has no issue, the formula does not change and the spreadsheet works as intended - just not for this one person. the cell is hidden and protected, the sheet is also password protected - it should not be possible for this cell to change but it does. Does anyone know why this might be happening? Both myself and the recipient of the spreadsheet run office 365 so there should be no issue with compatibility. as i say it is happening only to this one person, nobody else, and i do not understand why - looking for ideas on how to resolve this issue!!
3 Replies
It’s likely a version or sync issue. Ask the user to open the file in Excel Desktop (not web), turn off AutoSave, and ensure formulas aren’t auto-updating. Re-save as .xlsx this usually stops protected cells from changing.
Impossible to answer without seeing the file. And what is PBM?
PBM is play by mail - one cell changes so for example i might have the following formula in e1 "= A1+B1 +C1" - so the sum of three cells are added together - not 3 cells side by side as shown but just used as an example here but when the file has been transferred to this other person the formula in e1 suddenly reads "=a1+b2+c1" but because b2 is NOT a number cell but a text cell the result gives an error so it then disrupts further calcs that will need to read cells e1 answer. but this only happens to one person, all the others the formula stays correct. it is not restricted to the same cell, the next time it happens could be in cell Z5 rather than E1. so it is just not making sense
