Circular Reference with Formula but no reference to it

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!
Thanks in advance
Mark
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello ans welcome,

WHat cell is the formula in? I'm guessing B1.

Your formula has a reference to B1.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Where are you putting the formula?

What sheet name, and what cell ?
 

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8
B1=<TABLE style="WIDTH: 67pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=89 border=0><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=89 height=17>185%RPIT630</TD></TR></TBODY></TABLE>
not B2
 

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8

ADVERTISEMENT

QxTermage63 is a sheet that I am trying to find some number with column and row, Using a key I tried vlookup and circular reference then tried current version and still got circular reference.
b1 is key for the row and A1 is akey to the column
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Again, where are you PUTTING the formula? Which sheet and which cell contains that formula??
 

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8

ADVERTISEMENT

The formula is not on any of the sheets listed, it is in another sheet the other sheets are lookup sheets and the answer is derived from the variables entered. This is why am confused for and don't shout at people. I am asking a simple question the given the values A1 and B1 I get a number out of QxTermAge63,(the 63 can be any number and I have other sheets that can be used as lookup).
The formula sits on another page and by inputting the different variables I get the correct answer but with a circluar reference
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, to make it a little easier to read, I've taken out the INDIRECT and translated that using the information you gave.

=INDEX(QxTermAge63!$A:$DC,MATCH(B1,INDEX(QxTermAge63!$A:$DC,,1),0),MATCH(A1,INDEX(QxTermAge63!$A:$DC,1,),0))

Now, the only reasons for a circular reference is

1. The formula is entered in either A1 B1 OR somewhere in QxTermAge63!A:DC, or Quote Detail IP'!$C$10, or Quote Detail IP'!$C$5
which you say it's not

OR

2. There are OTHER formulas in any of those ranges listed above, that refer back to the cell this formula is entered in.

Look for not only standard formulas, but also look for formuas in
Data Validation
Conditional Formatting
Names (Insert - Name - Define)

I would suggest using Tools - Formula Auditing - Trace Dependants AND Trace Precedents.
 

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8
No other the only other formula is what makes up b1 which is

'Quote Detail IP'!$I$14&'Quote Detail IP'!$I$11&'Quote Detail IP'!$I$5&'Quote Detail IP'!$I$2&'Quote Detail IP'!$C$5&'Quote Detail IP'!$I$8
 

mrp100461

New Member
Joined
Mar 26, 2009
Messages
8
'Quote Detail IP'!$I$14&'Quote Detail IP'!$I$11&'Quote Detail IP'!$I$5&'Quote Detail IP'!$I$2&'Quote Detail IP'!$C$5&'Quote Detail IP'!$I$8 where
'Quote Detail IP'!$I$14=1
'Quote Detail IP'!$I$11=8
'Quote Detail IP'!$I$5=5%RPI
'Quote Detail IP'!$I$2=T
'Quote Detail IP'!$C$5=63
'Quote Detail IP'!$I$8=0
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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