Excel Circular Reference problem

[Antlarin]

The attached file shows a circular formula that will work for a few days and break for a few days and re-fix it's self. Its a never ending battle. I would really like to make it stop breaking but I cant seem to figure out my issue. Nor can I figure out why it would work only half the time.

When I start typing in cell D6 it should auto populate the other cells in the row except E6. E6 is manually inputted as well.

I need to keep F column a time stamp.

Every time I open the workbook I get a notification: Circular Reference Warning...

anyone have any ideas?

thanks,

[Keebellah]

Hi, a circular reference cannot fix itself so it either works or it doesn't.
I'll take a look at your file and see if I understand it

[JBeaucaire]

Turn this ON for your workbook:

File > Options > Formulas > [x] Enable iterative calculations

[Keebellah]

The formula in A5 points to itself and in B there REF errors

[JBeaucaire]

The formula in A5 points to itself .....

That's what circular references are, formulas that use their own address/value in their calculation. Iteration.

[Antlarin]

And I'm with you completely, But I used this formula in a Excel Doc this morning and after about 3 times the 4th started this back. Each time I opened the Excel Document and no warning popped up. On the 4th its gave me the pop up warning. This has been going on for months and just now getting around to asking for help.

[MrShorty]

F5 looks like you are using Chandoo's strategy for creating time stamps: http://chandoo.org/wp/2009/01/08/tim...-formula-help/ The only reason I can think for getting the "circular reference" error is that your "iteration" setting (in Excel options - formula) has been unchecked. Check your Excel options and make sure that this option is checked.

[Antlarin]

I did this and its giving me a #REF# error in the B and C columns. Am I missing something?

[Antlarin]

Turn this ON for your workbook:

File > Options > Formulas > [x] Enable iterative calculations

I did this and its giving me a #REF# error in the B and C columns. Am I missing something?

[MrShorty]

The formula in B5 has #Ref errors in it (=IF(D5<>"",IF(A5<>#REF!,1,#REF!+1),"") ) -- suggesting to me that the cell or cells or rows or columns or sheets referenced by this formula were deleted (not just cleared). C5 refers to B5, so the error value in C5 is just propagating the error in B5. What cell or cells was/were supposed to be referenced in B5? Edit the formula to replace the #REF! with the correct reference, and the #REF! errors should go away.

[Antlarin]

The formula in B5 has #Ref errors in it (=IF(D5<>"",IF(A5<>#REF!,1,#REF!+1),"") ) -- suggesting to me that the cell or cells or rows or columns or sheets referenced by this formula were deleted (not just cleared). C5 refers to B5, so the error value in C5 is just propagating the error in B5. What cell or cells was/were supposed to be referenced in B5? Edit the formula to replace the #REF! with the correct reference, and the #REF! errors should go away.

Well, Now I don't know what has happened. I didn't have the #REF until I change the setting to enable interactive calculations.

[Antlarin]

this is the formula in B5 in my working document:

=IF(D5<>"",IF(A5<>A4,1,B4+1),"")

[MrShorty]

I have no idea why changing the iteration setting should have replaced those references with the REF error -- Excel has never done that to me. I observe that row 5 is the only active row in the spreadsheet you attached to post #1, and it seems quite likely that one would have deleted (not just cleared) row 4 in prepping the sample file, which would have created those REF errors. This formula in B5 does not seem to be part of the circular logic, so I am not sure how much effort to spend on this formula where the main concern is the circular logic in A5 and F5.

[Antlarin]

So... I went back into settings and enabled circular calculations. I copied the original formulas from the top cell and re-pasted them. Saved, closed and reopened the workbook and cells are working and calculating fine now.Hopefully its fixed

Thanks Guys

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.