# Circular References

#### GrahamLawson

##### Board Regular
Hi,

In a sheet I use I want to add a new formula.

In column P I have the number of items outstanding for a given order. In Q I want to have a column to enter the number of units shipped in a month.

However, I then need P to adjust itself.

So if for example in P5 there are 10 outstanding units, and in Q5 I enter that 6 of these were shipped this month I need to P5 to take 6 away from the 10. I understand that this involves a circular reference, however I hjave not done anything with these before.

Any suggestions?

Thanks!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hello,

Circular references are where the computer will continue to do a caclucation and cannot stop e.g. in A1 if you had =A1-B1 and had values 100 in A1 and 1 in B1 the computer would try to make A1 99, but beause you are always subtracting 1 it will go 98, 97 and so on.

Can't you use a different column e.g. R so that Column P would still be OUTSTANDING UNITS, Q would be SHIPPED and R would be REMAINING?

I recommend you put in R5:

=P5-Q5

It's best to avoid circular references.

Hello,

Tis is probably not the best way to do this but have come up with some code that needs to go in the Sheet Code Window. Let me know how you get on.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Column = 17 Then
MY_ROW = Target.Row - 1
If MY_ROW = 0 Then
MY_ROW = 1
End If
OLD_VALUE = Range("a1").Offset(MY_ROW - 1, 15).Value
NEW_VALUE = OLD_VALUE - Range("a1").Offset(MY_ROW - 1, 16).Value
Range("a1").Offset(MY_ROW - 1, 15).Value = NEW_VALUE
Range("a1").Offset(MY_ROW - 1, 16).Value = ""
End If
End Sub

Make a column (R) link the column P to R then run all your necessary calcs of R, thus avioding circular references and providing some history and tracability of product shipment at the same time!

Come on!

Replies
3
Views
172
Replies
2
Views
135
Replies
12
Views
816
Replies
8
Views
148
Replies
5
Views
336

1,220,951
Messages
6,157,030
Members
451,392
Latest member
malcv

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