Excel table: mysterious change of formulae

[yky]

I have an Excel table which works fine most of the time. However, once in a while, I'd receive an error message saying "circular references" or some other formula error. It happened again this morning. The following are the formulae in cells T7, T8, T9, and T10.

T7: =IF($L7-$O7<>0,IF($E7="team",IF(ISERROR(LOOKUP(2,1/(T$5:T6<>0),T$5:T6)),0,LOOKUP(2,1/(T$5:T6<>0),T$5:T6))+$L7-$O7,0),0)

T8: =IF($L8-$O8<>0,IF($E8="team",IF(ISERROR(LOOKUP(2,1/(T$5:T7<>0),T$5:T7)),0,LOOKUP(2,1/(T$5:T7<>0),T$5:T7))+$L8-$O8,0),0)

T9: =IF($L9-$O9<>0,IF($E9="team",IF(ISERROR(LOOKUP(2,1/([COLOR=#ff0000]T$4:T9[/COLOR]<>0),[COLOR=#ff0000]T$4:T9[/COLOR])),0,LOOKUP(2,1/([COLOR=#ff0000]T$4:T9[/COLOR]<>0),[COLOR=#ff0000]T$4:T9[/COLOR]))+$L9-$O9,0),0)

T10: =IF($L10-$O10<>0,IF($E10="team",IF(ISERROR(LOOKUP(2,1/(T$5:T9<>0),T$5:T9)),0,LOOKUP(2,1/(T$5:T9<>0),T$5:T9))+$L10-$O10,0),0)

Other columns may also have problems. These cells contain a formula. Thus, they are not touched, no manual entering of data.

To the best of my knowledge, the errors happened after I sorted some columns by clicking the little triangle on the headers though it did not happen every time I sorted. Maybe some combination of sorting made this happened. I'm not sure.

Errors like this always make me wonder whether the workbook is corrupted.

 

[Gerald Higgins]

Hi, getting the circular reference warning does not mean that the workbook is corrupted, it means that one (or more) of your formulas refers to itself somehow.

This is called a circular reference.

A very simple example - if you put this formula in cell A1

=A1

you'll get the warning, because the formula refers to itself.

Sometimes circular references can be quite complicated to untangle - it may not be obvious how exactly the formula refers to itself.

In your examples, it looks pretty straightforward.

The formula you have got in T9 refers to cell T9.

 

[yky]

The formula you have got in T9 refers to cell T9.

Thanks for the reply but you didn't answer my question.

If you look at the four formulae I gave, it's obvious that the parts in red should be T$5:T8 and they WERE until Excel CHANGED THEM. I didn't change them.

My question is why Excel changed them. If I know why Excel changed them, maybe I can come up with a solution to keep the formulae from being changed.

This is not the first time this problem happened. This problem has happened several times, all on the this workbook.

 

[Gerald Higgins]

Hmmm . . .

Thanks for the reply but you didn't answer my question.

Re-reading the OP, I don't actually see a question.
If there was one, can you point it out to me please ?

. . . it's obvious that the parts in red should be T$5:T8

Maybe obvious to you but not to me.

I'll have a look to see if I can replicate this situation.

 

[Gerald Higgins]

To help me replicate this situation, which row(s) are your headers in ?
Do these formulas also extend ABOVE the cells quoted, eg. to T6, T5 and so on ?
If YES, please can you provide the formulas for those cells as well ?
Yes, I know, I can possibly guess them, but I'm also guessing that they might change as they approach the header row.

 

[SpillerBD]

To the best of my knowledge, the errors happened after I sorted some columns by clicking the little triangle on the headers though it did not happen every time I sorted. Maybe some combination of sorting made this happened. I'm not sure.

Errors like this always make me wonder whether the workbook is corrupted.

Yes the sorting. You have a mix of fixed and relative referencing in your formula. A Table with such formulas is not meant to be resorted, especially when those references are within the table's range.