If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I avoid circular reference AND extra input
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? |
#2
|
|||
|
|||
How can I avoid circular reference AND extra input
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? |
#3
|
|||
|
|||
How can I avoid circular reference AND extra input
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? |
#4
|
|||
|
|||
How can I avoid circular reference AND extra input
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? |
Thread Tools | |
Display Modes | |
|
|