×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Turn off updates in Excel equations

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
 

RE: Turn off updates in Excel equations

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.

RE: Turn off updates in Excel equations

'$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

RE: Turn off updates in Excel equations

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/
 

RE: Turn off updates in Excel equations

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources