Excel table: mysterious change of formulae

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,881
Office Version
  1. 2010
Platform
  1. Windows
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.

Code:
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.
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
Code:
=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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
  • Like
Reactions: yky
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top