My wife is a hotel breakfast hostess. Her boss wants her to keep a weekly
inventory of breakfast food items. In trying to EXCEL it I run into a
problem.
Three columns: Previous Inventory, Additions/Subtractions(+/-), New Inventory.

If I add +/- to Previous Inventory to get New Inventory I get a circular
reference problem.
To avoid this I figure she will have to input Previous Inventory (manually)
from New Inventory and then input +/- to get a 'new' New Inventory. Is there
a way to avoid the double manual input and avoid the circular reference
problem?

You shouldn't have a cirular reference. If the table is in columns A:C with
the header in row 1 and the first data in row 2 then:
A2 should have the starting inventory, B2 the first change (both are fixed
values) and C2 should have the calculated new inventory: =A2+B2.
In row three, the 'previous inventory' should be the prior row's 'new
inventory.' So in A3, =C2. B3 has the next update. And C3 is calculated
again as previous inventory + change: =A3+B3.
Now you should be able to copy the formulas from row 3 down to each new row,
only filling in the +/- in column B of each row, and with no circular
references.


"maintchief" wrote:

My wife is a hotel breakfast hostess. Her boss wants her to keep a weekly
inventory of breakfast food items. In trying to EXCEL it I run into a
problem.
Three columns: Previous Inventory, Additions/Subtractions(+/-), New Inventory.

If I add +/- to Previous Inventory to get New Inventory I get a circular
reference problem.
To avoid this I figure she will have to input Previous Inventory (manually)
from New Inventory and then input +/- to get a 'new' New Inventory. Is there
a way to avoid the double manual input and avoid the circular reference
problem?

I gave it a try and as you already know it worked. However with 40
inventoried items this growing for each item method becomes rather unwieldy.
Can you think of a way to keep the calculations on one line for each item?

"bpeltzer" wrote:

You shouldn't have a cirular reference. If the table is in columns A:C with
the header in row 1 and the first data in row 2 then:
A2 should have the starting inventory, B2 the first change (both are fixed
values) and C2 should have the calculated new inventory: =A2+B2.
In row three, the 'previous inventory' should be the prior row's 'new
inventory.' So in A3, =C2. B3 has the next update. And C3 is calculated
again as previous inventory + change: =A3+B3.
Now you should be able to copy the formulas from row 3 down to each new row,
only filling in the +/- in column B of each row, and with no circular
references.


"maintchief" wrote:

My wife is a hotel breakfast hostess. Her boss wants her to keep a weekly
inventory of breakfast food items. In trying to EXCEL it I run into a
problem.
Three columns: Previous Inventory, Additions/Subtractions(+/-), New Inventory.

If I add +/- to Previous Inventory to get New Inventory I get a circular
reference problem.
To avoid this I figure she will have to input Previous Inventory (manually)
from New Inventory and then input +/- to get a 'new' New Inventory. Is there
a way to avoid the double manual input and avoid the circular reference
problem?

You could add another column to indicate which product you're dealing with.
So now your four columns are Product, Previous Inventory, +/-, and New
Inventory.
The previous inventory calculation, starting in B2 could be
=sumif(a$1:a1,a2,c$1:c1). D2 would be =B2+C2. Only change now is that you'd
need to 'prime' this with an initial entry to each item to 'add' the starting
inventory; the sumif function calculates prior inventory by looking backward
and totalling all the +/-'s for that product.

"maintchief" wrote:

My wife is a hotel breakfast hostess. Her boss wants her to keep a weekly
inventory of breakfast food items. In trying to EXCEL it I run into a
problem.
Three columns: Previous Inventory, Additions/Subtractions(+/-), New Inventory.

If I add +/- to Previous Inventory to get New Inventory I get a circular
reference problem.
To avoid this I figure she will have to input Previous Inventory (manually)
from New Inventory and then input +/- to get a 'new' New Inventory. Is there
a way to avoid the double manual input and avoid the circular reference
problem?