Holger

New Member
Joined
Nov 22, 2017
Messages
5
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 :confused:

=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....
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Dim Me as xlNoob

Board Regular
Joined
Nov 12, 2017
Messages
107
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?
 
Last edited:

Holger

New Member
Joined
Nov 22, 2017
Messages
5
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

Board Regular
Joined
Nov 12, 2017
Messages
107
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Nov 22, 2017
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,346
Messages
5,641,598
Members
417,225
Latest member
LukiiMaxim96

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
Top