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