Circular Reference

[Holger]

Hey guys,

I know this is a long shot, but anyone got an idea why the below results in circular reference. The results are correct but I would just like to get ride of the error

=SUMPRODUCT(--((INDEX(INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$7&":$"&$B$19&"$"&$B$8),,MATCH(E$45,INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$20&":$"&$B$19&"$"&$B$20),0))=$A46)),--(INDEX(INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$7&":$"&$B$19&"$"&$B$8),,MATCH(E$44,INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$20&":$"&$B$19&"$"&$B$20),0))>=$B$9),--(INDEX(INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$7&":$"&$B$19&"$"&$B$8),,MATCH(E$44,INDIRECT("HeadcountFeed!$"&$B$18&"$"&$B$20&":$"&$B$19&"$"&$B$20),0))<=$B$10))

B7 = 2
B8 = 600
B9 = 1st day of month
B10 = last day of month
B18 = A
B19 = RL
B20 = 2
E44 =246
E45 = 247
A46 = SiteName

Thanks anyone who can help, much appreciated....

 

[Dim Me as xlNoob]

Sorry if I've missed it here, but what cell is your formula in? Guessing the all the data that the formula is referencing is on a different sheet to the formula and all the values listed in your post?

 

[Holger]

Formula is in E46. List of cells is on the same sheet. Only data referecned with HeadcountFeed is on a different sheet in the file....Hope this helps?

 

[Dim Me as xlNoob]

I'm sorry this doesn't really help, but using the formula and all the same data ranges you've provided I don't seem to get a circular reference error. Are the other values in the INDIRECT function formulas referencing something in the HeadcountFeed sheet or something?

 

[FDibbins]

Your formula is very complex and hard to follow/trace.
1st I would try replacing the INDIRECT's with their actual results, so that the whole thing becomes easier to read.
Then, if the circ ref still exists, start replacing/removing parts until it goes away - then you know where it is.

Seeing as all the references are actual values/entries and not formulas, it would appear that somewhere, you are referencing the cell that the actual formula resides in?

 

[Holger]

Thanks everyone. I got the problem resolved, which was that in one field an old formula remained with a wrong reference. Thanks for any comments.