• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Avoid a Circular Reference

Experts,

I have a field colored red in the attached.
I have a circular reference if use the formula N14-P14.

I am thinking there is someway to avoid the circular reference by modifying the formula?

thank you test.xlsx
0
pdvsa
Asked:
pdvsa
  • 4
  • 3
  • 2
2 Solutions
 
pdvsaProject financeAuthor Commented:
screenshot
0
 
Rob HensonFinance AnalystCommented:
The only way to avoid a circular reference (CR) in the formula is for column P to be a hard-coded value.

However, you can change your settings to allow CR.

Excel 2003 = Tools > Options > Calculation - Enable Iteration.

Not sure about the path for the same in 2007.

Thanks
Rob H
0
 
gowflowCommented:
what do you mean is there a way to avoid ? formula in P is depending on N and N is depending on P so it create obviously a circular reference.

Question in P can you get a formula not depending of N ?
gowflow
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
pdvsaProject financeAuthor Commented:
There is a way to store the value of N-P to avoid a CR.   I think I have seen this before.  I think this was on another website Mr Excel.  My memory is a little fuzzy about it though.  
0
 
gowflowCommented:
well one way to fix it is using vba I don't know if this suits you ?
gowflow
test.xlsm
0
 
gowflowCommented:
pdvsa did you check the file I posted ? would this solution be feasible to you ? You only need to carry over the formula in Col O and the code will take care of Col P.
gowflow
0
 
pdvsaProject financeAuthor Commented:
gowflow...thank you.  I had a very busy day yesterday.  It wasnt exactly what I was looking for but I am sure it will do the trick.  the issue is that it is saved as .xlsm and i want to send out to people and I know there is security issues with macros in excel and if they dont enable then the code might not work from what I remember.  Is that true?

thank you
0
 
Rob HensonFinance AnalystCommented:
Any code would require macros to be enabled before they work.

The xl2007 route for the iteration that I mentioned is:

Windows button > Excel Options > Formulas > Enable iterative calculation.

Thanks
Rob H
0
 
pdvsaProject financeAuthor Commented:
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now