excel when i copy formula from one cell from another cell getting this error Circular Ref

[Bitto]

error msg I am getting

Careful we found one or more circular ref in your workbook that might cause your formulas to calculate incorrectly .

can some one please help me with this msg why I am getting this msg, and why I cannot copy the formula from one cell to another cell.

for example have AD50 has 9.62 % will like to copy on J 28 same 9.62% so it can calculates and automates , if the AD50 changes than J28 can changes also.

how to fix this circular Ref error msg,

thanks ins advance .

[JBeaucaire]

You can use $ signs inside cell references in formulas to keep parts of those cells references from changing when you copy from cell to cell.

A1 - both row and column will change when copied
$A1 - row will change, column will not change when copied
A$1 - row will not change, column will change when copied
$A$1 - neither row nor column will change when copied

[Bitto]

thanks JBeaucaire

I tried this way $AD50 still getting error msg, one of the formula showing error msg,

formula I have

F28*(D28+E28*0.92)

F28 is cost ( D28+E28 qty .092 is Discount)

want to add formula 1*(5+0*.92) = $ 4.60 cost

some how circular ref giving me error msg

can you please tell me what's wrong with the formula , why I am getting error msg .

thanks

[Aardigspook]

A circular reference error implies that one of the cells you're referring to refers back to the cell you're trying to edit. Without seeing all the formulae affected it's difficult to know what could be causing the problem, so can you attach a file?
> Click 'Go Advanced' under the 'Reply' box then click the paperclip icon. Make sure the file doesn't contain any confidential information. Try to show us what's happening at the moment and what you want to happen - add notes to the file itself if that helps.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[KAB923]

Try going into File<Options<Formula and check off the block that says enable interactive calculations. I had this problem a few weeks ago, and it worked for me.

[Bitto]

please see attached the file,

AC51 cell wants to copy to J28 , its not copying to J28 and AC50 amount $ 155538 not copying on AD27 cell either

its giving me circular ref error msg , donot know what I am doing wrong,

any help will be great,

please help

[Aardigspook]

Tracing your formulae gives this:

J28 is

=+$AC$51

AC51 is

=+AD50

AD50 is

=+AC50/AC49

AC49 is

=SUMIFS(H$9:H$50,J$9:J$50,"<10%")

and AC50 is

=SUM(AC38:AC46)

The problem is that J28 is within the calculation for AC49, as it's within the range J$9:J$50.
If you change the AC49 formula to sum smaller ranges, it'll work:

Please Login or Register  to view this content.

Warning: I'm not a financial guy and don't know exactly what you need the spreadsheet to do - the above correction will fix the circular reference, but I can't guarantee exactly how your data will then work. But I hope the above helps.


Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[JohnTopley]

AC49 is the base cause of the problem: it has a SUMIF which references column J.

AD50=AC50/AC49 (and hence "references" Column J) and

AC51=AD50 (i.e. AC50/AC49) and hence references column J because of AC49.

So if you set J28=AC51 you will get a circular reference.

Confusing I know!

[Bitto]

thanks for reply , is there any solution to fix this circular ref or not

I did change my AC49 range to below ,

=SUMIFS(H$9:H$27,J$9:J$27,"<10%")

when I did this, it does not show any amount just 0 there ,

what I want to do in AC49 cell add all the amount from H column the one has SG& A rate less than 10% from J column
that's why I have that formula added ,

does any one have advise how to achieve this in AC49 and not to get circular ref also,

please some one can help me ,

that will be great

[Bitto]

thanks KAB923

I tried your solution, it worked

enable the interactive calculation check box

thanks every one for your help

[Aardigspook]

You're welcome - glad you got it working, and thanks for the rep.

[KAB923]

Your Welcome!!! I'm so glad, for some reason this check comes off, doesn't stay checked.

[Bitto]

you are right KAB923

it does not stay permanent
check box,
enable interactive calculations

only issue I have , because this I created users will be using it, every time this file open this msg pop up, circular ref error msg

is there any permanent solution for this ?

does any one have any idea, how to fix this ?

open to any advise,

please help,

[KAB923]

See if this helps.
http://answers.microsoft.com/en-us/o...218721a?auth=1