Circular Reference with Formula but no reference to it

[mrp100461]

=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

 

[onlyadrafter]

Hello ans welcome,

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

Your formula has a reference to B1.

 

[Jonmo1]

Where are you putting the formula?

What sheet name, and what cell ?

 

[mrp100461]

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]

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]

Again, where are you PUTTING the formula? Which sheet and which cell contains that formula??

 

[mrp100461]

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]

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]

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]

'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