Excel 2007: I had moved the main formula block out of the way, on the same sheet. Then reduced the column & row sizes outside of the work area. Everything worked.

I needed to modify the hidden code, so dragged it back to a workable area. 3 simple cell references were lost in the formulas. =(INDIRECT("'Carb-Data'!"&"B"&BV5))

BV5 was lost (another like this)


CW6 was lost

Clicking undo didn't fix the problem - the data returned to its previous position, but the refs were lost

I simply re-entered the cell references and 'filled' the columns. Everything works again.

I'd like to understand why these cell refs were lost when moving the data, when all others weren't. Eg. =F6&"_N1"

F6 was not lost.


I'm afraid I can't give you an exhaustive answers, however a few reasons:

  • All your references you've mentioned to be lost were relative references, so moving the cell containing the formula "moves" the reference too.
    • e.g. if your formula is in CW10 and you move it to CW8, then reference changes CW6 -> CW4
    • however, if you move CW10 -> CW1 then reference should change CW6 -> CW-3 which is definitely a wrong address, Excel converts it to #Ref
    • (in short you shift referred cells out of the sheet)
  • By moving a lot of cells, it's easy to loose track of referred ranges, if you delete a referred cell (delete entire row / column or delete and shift cells up / left), Excel will also give you #Ref error
  • Thanks Mate - Copy and paste works - Moving the block retains the top row reference - the 2nd row loses the ref - undoing the move fails - the ref still lost - I must remember to copy and paste (the failed undo is strange though) Sep 8 '17 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.