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