Forum Discussion
Formula copy error, when not copying formula
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?
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.
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
JoeUser2004thats 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
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.
