Turn off updates in Excel equations

[blindamood]

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

 

[IRstuff]

$b$4

TTFN

FAQ731-376

 

[joerd]

=INDIRECT("B4")

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[lind5496]

Thanks - The '$b$4' technique did not work. This is what the Excel Help had suggested, but for some reason it did not work. The =INDIRECT("B4") method does work, but for some reason I could not find this in the help section. Thanks for the help.

 

[brengine]

'$b$4' will also change to '$b$5' when inserting a row. The $'s only help when pasting a formula to additional cells. I don't think Excel will automatically do what you're wanting.

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

 

[IDS]

An alternative to the Indirect function is the Index function; e.g. replace + B4 with:

=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/

 

[IDS]

Correction: the function as shown will only give the desired result if the row is inserted between row 2 and row 4. If you insert a row under row 1 it beomes =INDEX(B3:B5,3) which refers to Row 5.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/