Circular referencing

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

I need help with a formula I am creating an inventory
data base and want to make a formula so that when I take
parts out..it is reflected in the quantity on hand. When I
go to create the formula it gives me the circular
reference error . I have tried changing the
iterations...but guess I dont know what I am doing really
as I cant make them work.

Here is the scenerio

Cell f20 contains qty on hand
Cell E20 contains qty removed my formula was

=F20-E20 this was in the F20 cell

Any help would be greatly appreciated

thanks
 
Either use an additional column for the result or use a worksheet_change
event that won't result in a circular reference.
 
Julie,

If this table is for one item only, then your Quantity On Hand formula
should subtract the Amount Taken from the Quantity on Hand of the prior row.
If this table contains all your various items, then you won't want to try to
put a transaction (taking out some of an item) into a table of inventory
items. It should go into another table. If you know Access, it may well
make a better platform for such an application.
 
To add to what I said.>right click on the sheet tab>view code>insert this>
Put the starting amount in cell A1. Now anytime you enter something in cell
B1, A1 will reduce by that amount

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
[a1] = [a1] - [b1]
End Sub
 
I did put in an extra column and that
worked..thanks...just wondering what a work sheet change
event is.?
 
Back
Top