Formula in Locked Cell Changing to #REF!

Occasional Visitor

Thanks for your help. 

I have created a spreadsheet that has basic arithmetic questions for my students to answer. I have locked all cells other than where they put their answers. The file is assigned through Microsoft Teams and nearly all of them come back perfectly normal. I have had 2 out of forty with the same problem, though for different cells and cannot figure out how it is happening.


Example of what they are doing:

5+7=_____ =if(a5=a1+a3, 1, 0) with a1 value being 5, a3 value being 7

There are many questions, all with this same format.

All cells are locked other than the answer cell

Total are then summed for marking


In each of the two problem worksheets, one of the cells containing the marking formula returns a REF error within the formula itself in place of the answer cell (in this case, a5)


For both of these worksheets, the error was for a different answer cell. I have tried to recreate the error in file I uploaded but have been unable to. I am at a loss because they all receive the same file and should not be able to change any of the formulae (I've checked and the cells are locked in the version they download as well as in the one they have uploaded for assessment). These are not tech-savvy kids so anything they are doing isn't on purpose.


Thank you!!

0 Replies