#REF! Error - copy/pasting data into cells, then cells on another sheet that = that cell displaying #REF!

[lichldo]

Hello!

I have a sheet (named "Raw Data by Month") where we copy/paste in daily financial data from another program. This raw data sheet is just meant for the purpose of housing the information. I have a bunch of other sheets, in the same excel document, that pull from those cells to display the information in different ways and do different calculations.

In those other sheets, I have it pulled the data from that main tab by using a formula such as - ='Raw Data by Month'!B3

However, after our data is copy/pasted in to that main data sheet, the other sheets display the #REF! error and we have to redo everything to link to the correct cells

Is there any way to avoid this happening? Everything is set up in a structure to where we are not changing any cell locations or formulas, the only thing changing is the daily data getting copy/pasted into that first sheet that everything else pulls from

 

[starl]

Before you paste the new data, are you Deleting the cells of old data? Highlighting the old Data, right-click, select Delete; or choose from the menu. Using the Delete key on the keyboard is Not the same.
If so - that will break the formulas. Instead, use ClearContents (or press the Delete key on the keyboard, which also just clears the contents of the cells)

 

[lichldo]

Before you paste the new data, are you Deleting the cells of old data? Highlighting the old Data, right-click, select Delete; or choose from the menu. Using the Delete key on the keyboard is Not the same.
If so - that will break the formulas. Instead, use ClearContents (or press the Delete key on the keyboard, which also just clears the contents of the cells)

Nothing is being deleted - the cells are blank until the data is copy/pasted in

 

[starl]

Is just the value shown #REF or has the formula in the cell been changed?

 

[lichldo]

Is just the value shown #REF or has the formula in the cell been changed?

ah, when clicking on the cell its showing as - ='Raw Data by Month'!#REF!

 

[starl]

ok - so that means the cell that is referenced is deleted.. but if you're just doing a copy/paste, no deletion involved.. I'm not sure what's going on.
Even if you (or your Mac) were doing an Insert, B3 would change to B10 (unless you were referencing the last cell/column on the sheet).
Could there be something strange in the source from which you're copying? Do a copy/paste to another sheet, then copy/paste from there - does it still happen?
Paste Special, Values, to drop any formatting that could be doing something strange?

or.. are you doing a CUT and paste?
That could cause what you are describing
Update: it only causes a #REF in the formulas if the cells are blank. If I'm overwriting the data, then everything is fine.

 

[lichldo]

ok - so that means the cell that is referenced is deleted.. but if you're just doing a copy/paste, no deletion involved.. I'm not sure what's going on.
Even if you (or your Mac) were doing an Insert, B3 would change to B10 (unless you were referencing the last cell/column on the sheet).
Could there be something strange in the source from which you're copying? Do a copy/paste to another sheet, then copy/paste from there - does it still happen?
Paste Special, Values, to drop any formatting that could be doing something strange?

or.. are you doing a CUT and paste?
That could cause what you are describing
Update: it only causes a #REF in the formulas if the cells are blank. If I'm overwriting the data, then everything is fine.

so we are copy/pasting (not cut) from a csv export from our finance system - so that shouldnt be coming with any other formatting etc

 

[starl]

Well, obviously this isn't how Excel works and it doesn't appear to be a user issue.
1. Are you using a 3rd party copy/paste program? Or definitely using Excel's menu options? If you've been using keyboard shortcuts, try the menu options.
2. Could IT have distributed something that's interfering.. I saw a mention of one company doing that, though the result was that copied formulas were being pasted as values automatically.
3. Have you tried creating a test workbook (as I've been doing). New workbook with 3 sheets - 1 for your "original data," 1 for your formulas, 1 for copy/paste the data (Raw Data by Month). I've also tested by putting the "original data" in a separate workbook and copy/paste to the Raw Data by Month. I also tested with a csv, but as you noted - those are clean files since they're text only. I've also tested with merged cells, blank Raw Data sheet, filled Raw Data sheet.
4. Do you have any addins installed? Have you tried testing with Excel in Safe Mode? Is that possible? I tried to find the steps, but all I can find is starting your Mac in Safe mode

 

[RoryA]

so we are copy/pasting (not cut) from a csv export from our finance system

And the csv is also open in Excel? Which version number/build of Excel do you have?