Sheet Protection and Circular Reference!!!!!

[Thomas5000]

One stupid thing i hate about Excel is the circular reference. Maybe it has a purpose but i don't see one. All i do is cut and past info in one cell to another and then the formula becomes a circular reference.

How can i avoid this? Does protecting the sheet prevent circular references from forming? How can i prevent circular references when cutting and pasting?

Thanks!

 

[Hasit]

Make the formulas absolute references (unless you need something else).

So if a formula reads

=A1+B1

absolute references would turn it to

=$A$1+$B$1

 

[SkipVought]

Hi,

Sheet protection has absolutely NOTHING to do with circular references.

Do you understand what a curcular refreence is?

In some cases, where a calculation tends to converge, they are okay. Most of the time its an error in logic that must be corrected.

Check Allow or correct a circular reference in Excel Help.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[combo]

When you cut&paste cells, the adresses in formulas in pasted cells do not change, whatever they are absolute or relative. The reason for circular reference could be that the chain of adresses in the pasted cells refers to the cell where you paste.

combo

 

[xlbo]

a circular reference is one that cannot be resolved by excel. As others have said it is nothing to do with sheet protection. It is excel's way of informing you that you have created a formula that cannot be resolved. You should be thankful it gives you this message as without it you would have a spreadsheet with incorrect formulae....

Try to understand something before calling it stupid ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Basically, it means that either direct or indirect, the formula result is based on itself. Excel does actually allow for this.

Under Options on the tab Calculation, you can check Iterations. Here you can set some parameters to allow excel to calculate it. Max number of Iterations and Max Change.

the first telling Excel to use the result of the formula X times to recalculate the end result and Max Change to tell Excel to stop iterating it the result changes with less than X.

Cheers,

Roel

 

[Thomas5000]

I think i understand circular reference. But i don't understand why Excel decides for itself when to change a formula i didn't ask to be changed.

Thanks for the reminder on absolute reference. I've used that before. I forgot. But i still don't understand why Excel changes formulas just because a person cuts and pastes. I KNOW i did not copy WITHIN the cell that had the formula. I only copied the cell content of the adjacent cells.

Thanks!

 

[SkipVought]

"i still don't understand why Excel changes formulas just because a person cuts and pastes."

Unless your references are ABSOLUTE, the row/column reference changes, AS DESIGNED & ADVERTISED. Works GREAT, if you understand.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[combo]

Are you talking about cutting or copying? It makes a difference. If the formula in A1 refers to A2, formula in A3 refers to A1, then, if you cut A3 and paste to A2, A1 will report circular reference. Without any change in A1.


combo