Formula copy error, when not copying formula

[TimPoulter]

OK I have an user with an odd error.  They are getting the following error

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly"

But the odd thing is we are not copying any formulas at all or the cells do not have a formula at all

Its just coping the value from once cell to another. Sometimes on the same sheet or even a different file.

This happens with the normal copy and paste or copy and paste only values

*edit* one thing I forgot to mention, it seems to only happen with 1 user, others who open the file it does not happen. and it happens randomly

[#2]

@TimPoulter 

It seems highly unlikely that the error message itself is in error. (That checking for circular references has been around for longer than you and I have been alive (a slight exaggeration, perhaps, but you get the point).

If it doesn't violate confidentiality, would it be possible for you to post a copy of the file in question, either on OneDrive or GoogleDrive?

[#3]

I'll see if I can get some sanitized versions, but the thing I forgot to mention in the original post is that is seems to be only affecting the 1 user, if someone else tries the same files it does not happen

[#4]

@TimPoulter wrote

the thing I forgot to mention in the original post is that is seems to be only affecting the 1 user, if someone else tries the same files it does not happen

And that omission is key to diagnosing what's happening. No need to post the file. Go find out what that one user is doing distinctively, uniquely. 

[#5]

@TimPoulter  wrote:  ``Its just coping the value from once cell to another``

The sudden appearance of the circ ref error has less to do with what is edited, and more to do with what is recalculated.

For example, if the Excel file references external data, differences in the external data might cause Excel to discover a circ ref due to a conditional formula.

Consider the following in a new workbook:

In A1, enter the formula =IF(ISODD(D1),B1,C1) .

In B1, enter the formula =A1 .

Because D1 is empty (it's a new workbook), ISODD is FALSE.  So Excel evaluates the false part that references C1 (no circ ref).

Now enter 1 (any odd number) into D1.

When Excel recalculates A1 (because it depends on D1), ISODD becomes TRUE.  So Excel evaluates the true part that references B1 (circ ref to A1), and it reports the (new) circ ref.

I say a "new" circ ref, because if we now replace 1 with 3, Excel does not report the circ ref again, even though A1 is recalculated, and B1 is still a circ ref to A1.

Presumably, the reason is:  Excel "remembers" that it reported the circ ref in B1, and that state has not just in the interim.

[#6]

@mathetes nothing out of the ordinary, they are job/po numbers and a price/cost from an excel sheet from the vendor, copy to a job tracking sheet that they use internally

@Joe Userthats the thing though. there is no formulas/formula results in the cells that are being copied so there should be no need to calculate anything. Its just a simple value

[#7]

@TimPoulter  wrote:  ``thats the thing though. there is no formulas/formula results in the cells that are being copied so there should be no need to calculate anything``

Re-read my previous posting and example more carefully.

1. My example does not copy a formula.  It changes a constant in D1.

Entering 1 into D1 is the same as copying 1 from E1 and pasting into D1, which is precisely what you described, to wit:  ``Its just coping the value from once cell to another``.

2. The "need to calculate" arises because a formula depends on the cell that was copy-and-pasted into.

In my example, A1 is recalculated because we change the constant in D1, and the formula in A1 references D1.  The change ("copy-and-paste") of the value in D1 causes A1 to behave differently, thereby uncovering the circ ref that was not evaluated before.

[#8]

@TimPoulter 

@mathetes nothing out of the ordinary, they are job/po numbers and a price/cost from an excel sheet from the vendor, copy to a job tracking sheet that they use internally

If it's happening only with one person, as you claimed in your earlier message, then that one person IS doing something out of the ordinary. By definition. The spreadsheet might not be different, but the copying and pasting--however that one person is doing it--is different. Or else the report is false.

[#9]

Ok I was able to get a hold of the user and watch what they are doing.

They left click and drag x number of cells (could be a 3x3 5x3 or what ever) the one thing the user did not mention is sometimes this selection has formulas at the source (but not at the destination)
Go to the other workbook
Select right click -> Paste Values

[#10]

@TimPoulter  wrote: ``nothing out of the ordinary, they are job/po numbers and a price/cost from an excel sheet from the vendor``

To clarify, @mathetes  said it correctly the first time:  not "out of the ordinary", but different ("distinctively").

Most likely, it is the different data that the one user is pasting that is uncovering the circ ref in some pre-existing formula(s).

No change to formulas; just changes to data.  Not changing the data in an "unordinary" manner; just changing different data in the ordinary manner.

In any case, the way to find the errant formula(s), go to the Excel file that reports the circ ref error, find the Error Checking feature (click Formulas > Error Checking in my version of Excel), click Circular Referencing, and look at the formulas of the identified cells.

It might not be very easy to track down the errant formula.  You might need to look at a chain of cell references.  Even so, Excel does not make it easy.

Consider my example again:  A1 has the formula =IF(ISODD(D1),B1,C1), B1 has the formula =B1, and D1 has the value 1.

Error Checking identifies both A1 and B1.  But using Evaluate Formula for both A1 and B1 merely shows that both formulas return zero(!).

It requires some skill to understand the formulas and realize that A1 might refer to B1, which refers to A1, and recognize that is a circ ref.  And often, the references are more complicated; for example, the circ ref might a whole-column reference like A:A.

IMHO, you probably need to enlist the aid of a knowledgeable Excel "programmer" in order to track down the problem.

[#11]

@TimPoulter  wrote:  ``sometimes this selection has formulas [...] Select right click -> Paste Values``

If the user is doing paste-value, it does not matter that the original cell value was derived from formulas.

And it does not matter how they are changing the data (e.g. ``left click and drag``).

I say again:  focus on differences in the data, and how the pre-existing formulas in the destination workbook behave differently because of the data.

It would be prudent for you to enlist the aid of a skilled Excel "programmer"; someone who understands Excel.  Otherwise, you are wasting your time.

Good luck!

[#12]

@Joe User

---

If the user is doing paste-value, it does not matter that the original cell value was derived from formulas.

I say again:  focus on differences in the data, and how the pre-existing formulas in the destination workbook behave differently because of the data.

---

That is the issue, there is NO formulas at the destination. they want to copy the values