#REF! Errors after copying formula to new sheet

**Big.Moe** · 05-30-2013, 09:29 PM

I'm sure someone has a one click solution to this, but I've been pulling my hair out trying to solve this issue:

After copying a block of cells to a new sheet, and using the REPLACE command under the EDIT menu to change the sheet references in the formula, all cells give me a #REF! error. The crazy thing is that I have checked the formulas and the references and they are all correct. On top of it, all I have to do to correct this is click on any cell with the error, then click on the formula bar (anywhere in the formula will do), and then press ENTER. No changing anything, just click-click-ENTER and whammo! no more #REF! error for that cell.

Here's the original of a sample formula:
=IF('Jan 12'!E$37=0,0,F$9/'Jan 12'!E$37)

and here is the copied version after changing sheet names:
=IF('Jan 11'!E$37=0,0,F$9/'Jan 11'!E$37)

Nothing earth shattering, so I'm stumped. If I only had a couple formulas to do this with, I would just go ahead and do it, but I have quite a few. Plus, I would like to know what's going on just for future reference (no pun intended).

Thanks.

**wenqq3** · 05-30-2013, 10:10 PM

how about press F9?

**Big.Moe** · 05-30-2013, 10:15 PM

Nope, tried that.

**wenqq3** · 05-30-2013, 10:25 PM

Can you upload a sample workbook with the #REF error? So we can check on it. Thanks

**Big.Moe** · 05-30-2013, 10:56 PM

You won't believe it, I saved a copy of my workbook and cleared those cells (because I had gone ahead & fixed them), then recopied the same exact block of cells to the same exact sheet, replaced all the sheet references, and now it works like a charm!!

Sorry, I just cannot reproduce the problem....weird.

This has happened to me before though. It's as if the references just do not update to the new cell on the new sheet after being copied, even though the sheet refs have been changed to the new sheet.

Oh well, I apologize for taking up your time.