+ Reply to Thread
Results 1 to 14 of 14

Prevent formula from updating when deleting cells / sheets

  1. #1
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105

    Prevent formula from updating when deleting cells / sheets

    Hi all,

    I'm sure this will turn out to be a quick one, but I'm really tired () and can't for the life of me think how I've done this in the past.

    I've got a workbook that I'm currently designing and I've just realised that I need to delete a few sheets, paste in some new template sheets and rename them. The problem is, I've already completed my summary sheets and I don't want to have to redo the values. Essentially, I just want to (temporarily) turn off auto-updating of formulas when cells / sheets are changed/moved/deleted.

    For practical purposes: I need to delete the sheet named "Jan", paste in a new sheet "MonthTemp", rename "MonthTemp" to "Jan" and have all my formulas not updated (e.g. still referencing "Jan" instead of "#REF!")

    Thanks as always for your assistance,
    Bob
    Last edited by beeawwb; 11-06-2008 at 01:28 AM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Try Tools > Options > Calculations and switch off automatic
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by oldchippy View Post
    Try Tools > Options > Calculations and switch off automatic

    Hi OldChippy,

    Just tried that; Turned it to manual, deleted the sheet, pasted the new one, renamed it to Jan. Went to the summary and it was still showing the old values (understandable, since Automatic calculation was turned off), turned Automatic back on, and it instantly showed #REF!. Perhaps it's linking to the Sheet value (Sheet19) instead of the SheetName?

    -Bob

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    If you go to your summary page and do a Find/Replace and look for = and replace with '= this will allow you to delete the old sheet, insert a new sheet and rename (exactly) as before. When completed inserting new sheets, go back to summary and do a reverse Find/Replace, all formulas should work as before

  5. #5
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by oldchippy View Post
    If you go to your summary page and do a Find/Replace and look for = and replace with '= this will allow you to delete the old sheet, insert a new sheet and rename (exactly) as before. When completed inserting new sheets, go back to summary and do a reverse Find/Replace, all formulas should work as before
    Hmm, worked for the first part, but not the second. (I think it's a great idea though, changing the formulas to text first!) I'll paste what my results are for 1 cell.

    Please Login or Register  to view this content.
    -Bob

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Yeh, I see your problem there, its finding the second equals sign in the formula and changing that one as well.

    If there are 36 replacements that's only actually 18 formulas, can't you pick those up by hand and put the ' in front of the = ?

  7. #7
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Heh heh, that's just summary sheet 1 I'm working on. I always work on the smaller one first. Sheet 2 has 247 to update. So, while I could do 247 formulas by hand (to remove the ') I'd rather not.

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    OK,

    How about Find/Replace with find =IF, replace '=IF

  9. #9
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by oldchippy View Post
    How about Find/Replace with find =IF, replace '=IF
    Same result as previously mentioned.

  10. #10
    Registered User
    Join Date
    10-25-2008
    Location
    Mount Joy, PA
    MS-Off Ver
    2003
    Posts
    44
    I was playing around with this idea, but ran into a problem when trying to remove the ' to make it a formula again. Excel kept saying that it couldn't find either ' or '=. How do you remove the ' ?

  11. #11
    Registered User
    Join Date
    10-25-2008
    Location
    Mount Joy, PA
    MS-Off Ver
    2003
    Posts
    44
    If you use "a" instead of ' you get the same results and then can remove "a" with replace.

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Try this pair of functions:
    Please Login or Register  to view this content.
    Last edited by shg; 11-05-2008 at 08:09 PM.
    Entia non sunt multiplicanda sine necessitate

  13. #13
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by shg View Post
    Try this pair of functions:
    Hi shg,

    The commenting function worked great. I thought there was an error (Application Error 1004) but I solved it using the Locals Window. I had an unrelated error in one of the hidden cells, evidenced by my debugging.

    Thanks for your help!

    -Bob

  14. #14
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You're welcome.

    Would you please mark the thread as solved?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1