Ok I am in a worksheet and I want to track inventory. So say one page is the inventory and the other page is the purchase quantity. What I want is to make the inventory quantity cell change as I change the purchase quantity. So that as we send out items, our inventory is automatically adjusted. What would the formula be, and is it possible? Can I make a cell work on a different page within a single workbook? As you can see I am not well trained in excel, so please try to keep answers in laymen's terms - yet explain so that I know what I am doing, and will be able to develop similar formulas in the future...thanks.
2007-07-26 06:35:13 · 6 answers · asked by christilicious 2 in Computers & Internet ➔ Software
It sounds like what you need to do is have a starting quantity of inventory, and then subtract the sum of sold quantities, to get the latest current inventory.
Let's say Sheet 1 has all your shipments out, with the quantities in column D (D2:D50).
And Sheet 2 has
B2: starting inventory quantity
C2: where you want to put the current quantity
In C2, you can would have this formula:
=B2 - SUM('Sheet 1'!$D$2:$D$50)
If you need more help, please elaborate or e-mail.
Good luck.
.
2007-07-26 06:52:55 · answer #1 · answered by aladou 5 · 0⤊ 0⤋
Inventory Tracking Excel
2016-12-29 16:33:45 · answer #2 · answered by ? 4 · 0⤊ 0⤋
Sheet numbers are supposition.
As I understand, Sheet 1 contains the original inventory which may be entered from a physical inventory. Say Cell A5.
I assume that Sheet 1 is a single item or product.
Sheet 2 contains receivings. Say Cell A5.
Sheet 3 contains items shipped out. Say A5.
Place the cursor in a separate column, (Say B5), in Sheet 1.
Open Sheet 2. With the cursor on the cell A5, showing quantity received, Use Copy.
Go to Sheet 1, in new cell B5 and "Paste, Special".
You will see on the menu, "Paste Link". The cell will show a link to Sheet 2 where the cell contains the quantity received.
In front of that formula, enter, =A5+(The formula). This will continuously add the receivings to the original inventory.
Next, Open Sheet 3. Place the cursor on the cell A5 showing quantity shipped.
Copy. Open Sheet 1.
Very important, do not paste to cell B5. This will overwrite the existing formula.
Go to any BLANK CELL, not part of any calculations.
"Paste Special" to that blank cell. This is again, a link to the quantity shipped.
NOW, at Cell B5 where you now have the link to receivings plus the original inventory, At the end of the formula/link, place a -, minus.
Do not copy, TYPE, after the minus, THE LINK AS IT IS.
Your formula should look like this on Sheet 1, Cell B5.
=A5+(Link to Receivings)-(Link to Shippings).
If this is not clear or doesn't work for some reason, contact me.
If you have new receivings in more than one cell, such as different dates, you will have to Paste a link from each of those and use + each time.
DO NOT PASTE A LINK over the existing formula. Copy to a blank cell and TYPE THE LINK to the formula.
When you have shipping in more than one cell, it would be best to have a "sum cell" on that sheet, and paste a link from there in Sheet 1.
2007-07-26 07:22:00 · answer #3 · answered by ed 7 · 0⤊ 0⤋
Inventory Tracker Excel
2016-11-07 07:29:19 · answer #4 · answered by ? 4 · 0⤊ 0⤋
in case you're doing this for homework or some style of try, then i might take the 1st answer's elementary advice as that's interior the the suitable option ballpark. Use subtraction and conditional formatting. If that's a actual issue, then i may be extra suitable than chuffed to assist you offline. i'm a microsoft qualified expert in EXCEL and ought to repair a sheet for you at as quickly as and at no value in case you touch me. I do exactly no longer desire to spend time on something it is meant on your homework or a classification, and so on.
2016-11-10 08:41:44 · answer #5 · answered by Anonymous · 0⤊ 0⤋
on the cell you want to info to be copied to press the = key then navigate with the mouse to the cell you want to get the information from (even though it is on another worksheet) and then press enter
2007-07-26 06:40:13 · answer #6 · answered by cool_clearwater 6 · 0⤊ 0⤋