Moving or deleting cells caused an invalid cell reference

June 7, 2017 at 12:03:01
Specs: Windows 7
I'm trying to create a workbook template, where one sheet contains the final formatted report with cell references to another sheet with the raw data. My plan was to leave the data sheet empty for the template, and then to build a report just copy and paste the raw data into the sheet and have the report auto-populate, but when I paste in new data, I get a series of #REF errors saying "moving or deleting cells caused an invalid cell reference, or function is returning a reference error". How can I fix this?

See More: Moving or deleting cells caused an invalid cell reference

Report •

#1
June 7, 2017 at 12:38:23
Since we can't see your workbook or watch your process from where we are sitting, it's kind of hard for us to diagnose your problem or offer a solution.

What do you mean by "paste the raw data into the sheet"? Where is the raw data coming from? Is it being copied from a worksheet with the same format as the destination, i.e. merged cells, references to other workbooks/worksheets, etc?

Have you tried Paste Values to eliminate all cell references in any copied formulas? Some more details might help.

Obviously, I don't know if these questions even apply, since I don't know what you are doing when you say "when I paste in new data".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 7, 2017 at 12:55:14
The first sheet (called Report) is my final formatted report, which contains formulas with references to cells on the next sheet (called Data), which contains raw data (just numbers, no formulas) that are copied and pasted from a different software program. Example: a cell on the Report sheet contains the formula: =SUM(Data!B2:B4). This works fine, but when I change the numbers on the Data sheet to create a different report, I get the "moving or deleting cells caused an invalid cell reference..." error.

So I'm not copying any formulas, I'm just changing the values of the cells that are referenced in my formulas (or trying to at least), so that I can have a final formatted report that will auto-populate when the numbers on the Data sheet change.

Let me know if I can provide any more details!


Report •

#3
June 7, 2017 at 14:11:26
The #REF error is always a pain, but from your description, see the last video on this page;

http://excel-example.com/other-tuto...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
June 7, 2017 at 16:21:18
You are going to have to be more specific as to your process and where the data is coming from. I'll tell you what I just did, which worked without any errors. You are mostly likely doing something different because your process is not working for you.

I created 2 Sheets:

Report and Data

In Report!A1 I placed this formula:

=SUM(Data!B2:B4)

In Data!B2:B4 I entered 1, 2 & 3, respectively. I would expect Report!A1 to display 6 and it does. All good so far.

I then went to the following web page and copied the first 4 rows of numbers. These numbers were then pasted into Data!A1.

https://en.wikipedia.org/wiki/List_...

Since Data!B2:B4 now contain 11, 21 & 31, I would expect Report!A1 to display 63. It does. All still good.

I then copied the next 4 rows from that page and pasted it over the numbers in Data, again starting in Data!A1.

Since Data!B2:B4 now contain 51, 61 & 71, I would expect Report!A1 to display 183. It does. All still good.

So, it seems to me that your raw data, which is "copied and pasted from a different software program", isn't just a simple copy/paste of numbers. There appears to be something else going on, such as perhaps the cells of a table from the "different software program" are replacing the cells in the Excel sheet, thus causing the error. I'm not talking about the numbers replacing the numbers, I'm talking about the actual cells themselves being replaced. That's one of the reasons I asked whether you have tried using Paste Values so that (hopefully) only the numbers themselves get pasted into the Data sheet.

Without more info (i.e. precise details such as I provided) there's not much else I can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
June 13, 2017 at 17:07:37
The data that I am copying into the cells is already being done using Paste Values.

I followed your steps above exactly, and everything worked great. The issue that I then run into is that when I save, close, and reopen the file after changing the data as you did above, I run into the "invalid cell reference" error...


Report •

#6
June 13, 2017 at 19:13:48
re: "I followed your steps above exactly"

Exactly as in created a new book and then used the Wikipedia data?

You run into the error when using your data or when using the data from the Wikipedia page? I am having no problems Saving, Closing, Opening the file with the Wikipedia data.

If you are having problems even when using the Wikipedia data, you may have some sort of corruption. Without a copy of your workbook to play with, there is not much else I can offer.

If you can upload a copy of the workbook to zippyshare.com - after removing all personal/confidential data - and then tell us where you are getting your data from, maybe we can replicate the problem and figure out what is going on.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question