Hi,
I searched for circular reference issues as I have the problem facing me now. I saw Adam's post from 3/8 of this year and the response formula that was given. I have applied that to the existing formula I am working adjusting to fit the needs and have no success. Here is the IF as it is not in the workbook for Cell D5
=IF(Data!$C5="Analysis", IF(Data!$G5>D5,Data!$G5,D5),D5) the italicized nested IF is the formula from the above mentioned post just adjusted to fit my scenario. All I get is a zero, no matter what the value of the target cell D5 may be. Even if it is a zero there is still no value change.
I have attached the test file. And have carefully documented it. On the "Analysis" sheet the 3 test conditions are laid out along with the expected results for cell D5. The source data is on the "Data" page. Presently I know that the use of the target cell for the FALSE condition in the outer IF works to preserve what ever value is in the cell should the Status not be "Analysis", at least that works.
Hello,
Normally you avoid to write formulas resulting in a circular reference, but there is an option to more or less allow such.
See if enabling iterative calculation works for you, in my tests it doesn't to the fully, I do get 7 as a result, but when changing the number in Data sheet it does not update.
If you haven't found this page already, here is a support page describing a little more of this option.
In my view you have two options, using VBA or add data (new rows) per status change.
Br,
Anders
Anders.
Thank you for the response. I reviewed the page to tagged in the response and determined that the issue could be resolved by limiting the number of iterations of the formula. Once I enabled iterative calculation and dropped it down to 10 from 100 I had no further issues and my target table is now filled out correctly with current and historical values.
Regards,
William
Hello,
Great that it works and thanks for your update, I have avoided such scenarious but with your example I see that it can actually work.
Br,
Anders
