Turn off updates in Excel equations
Turn off updates in Excel equations
(OP)
I want to turn off the 'auto update' function in my Excel Spreadsheet, and I can't figure out how.
Example:
Cell A1 is '=B4'
There are numbers in all of the B column.
I insert a new cell above B2. Typically, Cell A1 updates to '=B5'. I want it to stay '=B4'
Any clue how to do this?
Thanks
Example:
Cell A1 is '=B4'
There are numbers in all of the B column.
I insert a new cell above B2. Typically, Cell A1 updates to '=B5'. I want it to stay '=B4'
Any clue how to do this?
Thanks





RE: Turn off updates in Excel equations
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Turn off updates in Excel equations
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Turn off updates in Excel equations
RE: Turn off updates in Excel equations
A couple work arounds I can think of are:
1) Insert your cell then Search/Replace B5 for B4. (Obviously not ideal if you have a lot of different cells you want to change the references for.)
2) Insert your cell then Cut B5, and Paste to B4. (Again...Obviously not ideal if you have a lot of different cells you want to change the references for.)
3) Copy your WorkSheet. Insert our cell in the original WorkSheet. Go to the copied version, then Copy the cells containing the Formula's you didn't want to update. Now Paste the formula cells back into the original WorkSheet.
-->While this will do what you originally requested...I'm guessing though, that you will find there were actually some cells with formula's that you did want to automatically update...But maybe not...But you're probably safer if you just go just go with one of the first 2 suggestions, knowing there will be manual updates you'll have to plow through.
Enjoy,
Ken
RE: Turn off updates in Excel equations
=INDEX(B2:B4,3)
If you insert a row between Row 1 and Row 4 the Index will adjust to =INDEX(B2:B5,3) but it will still refer to cell B4 (3rd row down from the top of the specified range)
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Turn off updates in Excel equations
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/