Prevent formula from updating when deleting cells / sheets

**beeawwb** · 11-05-2008, 06:32 PM

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

**oldchippy** · 11-05-2008, 06:37 PM

Try Tools > Options > Calculations and switch off automatic

**beeawwb** · 11-05-2008, 06:41 PM

Originally Posted by oldchippy

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

**oldchippy** · 11-05-2008, 06:57 PM

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

**beeawwb** · 11-05-2008, 07:09 PM

Originally Posted by oldchippy

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

**oldchippy** · 11-05-2008, 07:15 PM

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

**beeawwb** · 11-05-2008, 07:17 PM

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.

**oldchippy** · 11-05-2008, 07:21 PM

OK,

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

**beeawwb** · 11-05-2008, 07:39 PM

Originally Posted by oldchippy

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

Same result as previously mentioned.

**whallgren** · 11-05-2008, 07:52 PM

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

**whallgren** · 11-05-2008, 07:55 PM

If you use "a" instead of ' you get the same results and then can remove "a" with replace.

**shg** · 11-05-2008, 07:58 PM

Try this pair of functions:

Please Login or Register  to view this content.

**beeawwb** · 11-06-2008, 01:15 AM

Originally Posted by shg

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

**shg** · 11-06-2008, 01:30 AM

You're welcome.

Would you please mark the thread as solved?

Prevent formula from updating when deleting cells / sheets

LinkBack

Thread Tools

Rate This Thread

Display

Prevent formula from updating when deleting cells / sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions