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

 

[Seifeddine87]

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.

thank you very much for your help

 

[Jonmo1]

I don't understand.
a Circular Ref is a Circular ref. You can't just make it 'Not be' a Circular ref anymore.
The formula in B depends on the value of A.
Putting a formula in A that depends on the value of B creates a circular reference.
This is an absolute truth that cannot be changed.

You CAN make excel ignore the fact that it's a circular reference.
Though I doubt this would have the results you're hoping for.
In File - Options - Formulas
Put a check on 'Enable iterations' and choose a max number of iterations (I have no advice on which number to choose, probably the lower the better)

 

[Seifeddine87]

I don't understand.
a Circular Ref is a Circular ref. You can't just make it 'Not be' a Circular ref anymore.
The formula in B depends on the value of A.
Putting a formula in A that depends on the value of B creates a circular reference.
This is an absolute truth that cannot be changed.

You CAN make excel ignore the fact that it's a circular reference.
Though I doubt this would have the results you're hoping for.
In File - Options - Formulas
Put a check on 'Enable iterations' and choose a max number of iterations (I have no advice on which number to choose, probably the lower the better)

ok i will explain what i need that formula for


i have split my excel into multiple files so my staff be able to use it at the same time

Detail file 1

on the item location column am trying to do the index and match formula to find the right location name based on the free space and group

[TABLE="width: 467"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]Weight P/Kg[/TD]
[TD="colspan: 4"]Size[/TD]
[TD]Item Location[/TD]
[TD]Location Group[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]W[/TD]
[TD]H[/TD]
[TD]Dim[/TD]
[/TR]
[TR]
[TD]507[/TD]
[TD]1.000[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]360[/TD]
[TD]B-5-D[/TD]
[TD]G1[/TD]
[/TR]
[TR]
[TD]508[/TD]
[TD]1.500[/TD]
[TD]45[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]20250[/TD]
[TD] A-1-B[/TD]
[TD]G1[/TD]
[/TR]
[TR]
[TD]509[/TD]
[TD]2[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]18000[/TD]
[TD] A-1-A[/TD]
[TD]G2[/TD]
[/TR]
</tbody>[/TABLE]

Stock file 2

every thing connect from file 1 to file 2

[TABLE="width: 409"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]Weight P/Kg[/TD]
[TD="colspan: 4"]Size[/TD]
[TD]Item Location[/TD]
[TD]Location Group[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]W[/TD]
[TD]H[/TD]
[TD]Dim[/TD]
[/TR]
[TR]
[TD]507[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]360[/TD]
[TD]B-5-D[/TD]
[TD]G1[/TD]
[/TR]
[TR]
[TD]508[/TD]
[TD]1.5[/TD]
[TD]45[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]20250[/TD]
[TD]A-1-B[/TD]
[TD]G1[/TD]
[/TR]
[TR]
[TD]509[/TD]
[TD]2[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]18000[/TD]
[TD]A-1-A[/TD]
[TD]G2[/TD]
[/TR]
</tbody>[/TABLE]

Sale file 3

every think i have sold in this file connected with file 2 with vloockup

[TABLE="width: 157"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]Dim[/TD]
[TD]item location[/TD]
[/TR]
[TR]
[TD]L[/TD]
[/TR]
[TR]
[TD]507[/TD]
[TD]360[/TD]
[TD]B-5-D[/TD]
[/TR]
[TR]
[TD]508[/TD]
[TD]20250[/TD]
[TD]A-1-B[/TD]
[/TR]
[TR]
[TD]509[/TD]
[TD]18000[/TD]
[TD]A-1-A[/TD]
[/TR]
</tbody>[/TABLE]

storage file 4 Selves names and size

the column Used is (sumif stock) - (sumif sale) and it came back with the right value but when i do the formula on file 1 start having the Circular problems

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl70, width: 64"]Group[/TD]
[TD="class: xl70, width: 64"]Free[/TD]
[TD="class: xl70, width: 64"]Used[/TD]
[TD="class: xl70, width: 64"]Total[/TD]
[TD="class: xl70, width: 64"]Location[/TD]
[TD="class: xl70, width: 64"]L[/TD]
[TD="class: xl70, width: 64"]W[/TD]
[TD="class: xl70, width: 64"]H[/TD]
[/TR]
[TR]
[TD="class: xl69"]G1[/TD]
[TD="class: xl63"]233200[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl63"]233200[/TD]
[TD="class: xl63"]A-1-A[/TD]
[TD="class: xl63"]53[/TD]
[TD="class: xl63"]44[/TD]
[TD="class: xl63"]100[/TD]
[/TR]
[TR]
[TD="class: xl68"]G2 [/TD]
[TD="class: xl65"]114400[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"]114400[/TD]
[TD="class: xl65"]A-1-B[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]44[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl68"]G1[/TD]
[TD="class: xl65"]127600[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"]127600[/TD]
[TD="class: xl65"]A-1-C[/TD]
[TD="class: xl65"]29[/TD]
[TD="class: xl65"]44[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl68"]G2 [/TD]
[TD="class: xl65"]112500[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"]112500[/TD]
[TD="class: xl65"]A-1-D[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]50[/TD]
[TD="class: xl65"]90[/TD]
[/TR]
</tbody>[/TABLE]


all what am trying to do is to give me the right location name so the staff stop putting the items in one location and wright down different location as they day dreaming must the time