how to fix Error reference to a cell dependent on its own cell value

[Seifeddine87]

hi am using Index, match function and this message keeps coming up

careful we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly
fyi a circular reference can be a formula that refers to its own cell value, or reference to a cell dependent on its own cell value

when i change the formula to normal numbers every thing works perfect but when i change it back it come up with this the same message and value is 0

is there any chance to make it work in a formula thank you

 

[Jonmo1]

What is the actual formula, and which cell are you putting it in?

 

[Seifeddine87]

=INDEX('[09 Vehicles & Storage Data.xlsx]Storage'!$F$7:$F$290,INDEX(MATCH(1,('[09 Vehicles & Storage Data.xlsx]Storage'!$B$7:$B$290=I4)*('[09 Vehicles & Storage Data.xlsx]Storage'!$D$7:$D$290>=G4),),0))

$D$7:$D$290 there is a =(SUMIF('[04 Stock.xlsx]Stock'!$H$3:$H$10003,F7,'[04 Stock.xlsx]Stock'!$G$3:$G$10003))-(SUMIF('[05 Sale.xlsx]Data'!$T$4:$T$4814,F7,'[05 Sale.xlsx]Apr'!$U$5:$U$404))

 

[Jonmo1]

Please answer both questions.

What is the actual formula, and which cell are you putting it in?

 

[Seifeddine87]

Please answer both questions.

In [01 Detail.xlsx]Detail Cell H4 i have put this formula
=INDEX('[09 Vehicles & Storage Data.xlsx]Storage'!$F$7:$F$290,INDEX(MATCH(1,('[09 Vehicles & Storage Data.xlsx]Storage'!$B$7:$B$290=I4)*('[09 Vehicles & Storage Data.xlsx]Storage'!$D$7:$D$290>=G4),),0))

in [09 Vehicles & Storage Data.xlsx]Storage D7 i have put this formula
=IF(F7="","",SUMIF('[04 Stock.xlsx]Stock'!$H$3:$H$10003,F7,'[04 Stock.xlsx]Stock'!$G$3:$G$10003)-SUMIF('[05 Sale.xlsx]Data'!$T$4:$T$4814,F7,'[05 Sale.xlsx]Apr'!$U$5:$U$404))

 

[Seifeddine87]

n [09 Vehicles & Storage Data.xlsx]Storage D7 i have put this formula
=IF(F7="","",SUMIF('[04 Stock.xlsx]Stock'!$H$3:$H$10003,F7,'[04 Stock.xlsx]Stock'!$G$3:$G$10003)-SUMIF('[05 Sale.xlsx]Data'!$T$4:$T$4814,F7,'[05 Sale.xlsx]Apr'!$U$5:$U$404)) The value =3500

but when i delete the formula and i wright 3500 its working fine

 

[Seifeddine87]

just looking for a way how to make it look like value not formula so the other formula can read it probably thank you for your help

 

[Jonmo1]

Yes, that's a circular reference.
The formulas in [09 Vehicles & Storage Data.xlsx]Storage column D are dependent on the value of [01 Detail.xlsx]Detail Cell H4

You'll need to put the formula outside any of those referenced ranges to avoid the circular reference.

 

[Jonmo1]

just looking for a way how to make it look like value not formula so the other formula can read it probably thank you for your help

You could put the formula in another cell that doesn't create a circular reference. Then Copy that cell and paste-special values onto the original cell.

 

[Seifeddine87]

yes but that formula keep changing all the time is there any way i can bypass that error and just let it read it as numbers