# Circular Reference with Formula but no reference to it

#### mrp100461

##### New Member
=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!!
Mark

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hello ans welcome,

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

Your formula has a reference to B1.

Where are you putting the formula?

What sheet name, and what cell ?

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

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

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

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

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.

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

'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

Replies
5
Views
132
Replies
2
Views
133
Replies
1
Views
334
Replies
2
Views
135
Replies
7
Views
131

1,203,674
Messages
6,056,680
Members
444,881
Latest member
Stu2407

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

### Which adblocker are you using?

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

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