2

When I update Excel to the latest figures my formulas keep moving with the old data. I have already tried using absolute and or relative references like $A$1, $A1, A$1, and A1. I've also tried using the protect feature, with no luck. Can anyone help with this? Thus far I've just had to lose half a day changing cell references back and macros may or may not damage the sheet.

Trav
  • 1,168
Melvin Decker
  • 21

1 Answers1

1

One way to prevent the formulas from being altered as you alter the data is using indirect addressing Basically, if you have this formula

=SUM(A1:A10)

it will be altered when you copy/paste the referenced cells. To prevent this, you can replace it by

=SUM(INDIRECT("A1:A10"))

Once you specify your range as a string, Excel will not be able to track it back to the referenced cells anymore.

Dmitry Grigoryev
  • 9,861