Please can someone help if what I am asking is feasible)
I have a spreadsheet with a few tabs:
one tab is keeping track of stock balances as well as cost price (first upload) tab called 'Stock'
another tab tracks purchases (second upload) tab called 'Purchases
What I would like to achieve is if products are purchased that already exist (entered into Purchase tab) - add these to Qty already in the Purchased column (F) of the 'Stock' tab
If there is a difference in Price (Column E in purchase tab) - calculate the new average cost into the Stock tab sheet (Column C) - this shouldn't alter the cost price of any items already previously sold which are in recorded in another tab called Sales
If the way to process this required adding columns that would be fine.
Thank you for your help
Marc
Stock tab
Purchase tab
I have a spreadsheet with a few tabs:
one tab is keeping track of stock balances as well as cost price (first upload) tab called 'Stock'
another tab tracks purchases (second upload) tab called 'Purchases
What I would like to achieve is if products are purchased that already exist (entered into Purchase tab) - add these to Qty already in the Purchased column (F) of the 'Stock' tab
If there is a difference in Price (Column E in purchase tab) - calculate the new average cost into the Stock tab sheet (Column C) - this shouldn't alter the cost price of any items already previously sold which are in recorded in another tab called Sales
If the way to process this required adding columns that would be fine.
Thank you for your help
Marc
Stock tab
| StockName | Column1 | Item Cost | Mark up | Sale Price | Qty Purchased | Sold | Stock | Value of Stock |
| Amazon Echo | Active | £17.00 | £2.00 | £19.00 | 20 | 6 | 14 | 238 |
| Anker Nebula | Active | £319.99 | £30.00 | £349.99 | 15 | 7 | 8 | 2560 |
| BaByliss Berry Crush Straightener | Active | £28.00 | £28.00 | 2 | 1 | 1 | 28 | |
| Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly… | Active | £7.49 | £7.49 | 10 | 1 | 9 | 67 | |
| Bush Big LED Alarm Clock Radio | Active | £14.99 | £14.99 | 3 | 0 | 3 | 45 | |
| Canon MG3650S WH… | Active | £39.99 | £39.99 | 1 | 1 | 0 | 0 | |
| Canon PIXMA TR4551 | Active | £49.99 | £49.99 | 0 | 0 | 0 | 0 | |
| Canon PIXMA TS5151 3-in-1 Printer - White… | Active | £59.00 | £59.00 | 2 | 0 | 2 | 118 |
Purchase tab
| Date | Stock Name | Purchase Price | Previous Price | Difference in Price | Bought QTY | Total cost |
03/01/2022 | Heroes of Goo Jit Zu SUPAGOO DINO… | 10.00 | 10.00 | 0.00 | 4 | 40.00 |
09/01/2022 | Amazon Echo | 16.50 | 17.00 | -0.50 | 2 | 33.00 |
06/01/2022 | Anker Nebula | 319.99 | 319.99 | 0.00 | 2 | 639.98 |
07/01/2022 | BaByliss Berry Crush Straightener | 23.00 | 28.00 | -5.00 | 1 | 23.00 |
08/01/2022 | Baylis & Harding Jojoba, Vanilla & Almond Oil Luxury Bathing Duo Stack Gift Box Gift Set - Vegan Friendly… | 22.00 | 7.49 | 14.51 | 1 | 22.00 |
10/01/2022 | CLARINS Body Care Set 3Pc | 56.00 | 23.10 | 32.90 | 1 | 56.00 |
05/01/2022 | Pixi Best of Vitamin C- Mask, Toner & Moisturiser… | 13.00 | 9.99 | 3.01 | 1 | 13.00 |
