Monkeyman50
New Member
- Joined
- Jan 20, 2019
- Messages
- 5
Hi There,
I have a bit of a dilemma with an excel sheet I'm working on and was hoping for a bit of advice from the experts on here.
I am trying to create an excel workbook that can manage my inventory, record sales and stock purchases and display some figures in a nice little dashboard.
Where I'm getting stuck is by trying to create a First In First Out table for my products. So for example I have bought 10 chairs at £2 and then 2 weeks later I've bought 15 chairs at £5 as the price has gone up, now if someone has bought 12 chairs from me in one sale, I need the sheet to minus 10 at £2 and then realising that "pool" of stock is finished minus 2 at £5 and return the total cost of £30 in the sales sheet so I can accurately see profit. I almost had a solution but using only formulas (lots of IFS and INDIRECTS), as i'm a novice with VBA, but it doesn't quite work and the sheet is very slow.
I've been trying to create a stock table for each product I have and in the near future I could have over 1000 products. Should I be using access for my product database or would excel cope with this amount of data without slowing to a crawl? If the proper method / VBA etc was used as a solution.
Any advice is appreciated.
Thank you,
I have a bit of a dilemma with an excel sheet I'm working on and was hoping for a bit of advice from the experts on here.
I am trying to create an excel workbook that can manage my inventory, record sales and stock purchases and display some figures in a nice little dashboard.
Where I'm getting stuck is by trying to create a First In First Out table for my products. So for example I have bought 10 chairs at £2 and then 2 weeks later I've bought 15 chairs at £5 as the price has gone up, now if someone has bought 12 chairs from me in one sale, I need the sheet to minus 10 at £2 and then realising that "pool" of stock is finished minus 2 at £5 and return the total cost of £30 in the sales sheet so I can accurately see profit. I almost had a solution but using only formulas (lots of IFS and INDIRECTS), as i'm a novice with VBA, but it doesn't quite work and the sheet is very slow.
I've been trying to create a stock table for each product I have and in the near future I could have over 1000 products. Should I be using access for my product database or would excel cope with this amount of data without slowing to a crawl? If the proper method / VBA etc was used as a solution.
Any advice is appreciated.
Thank you,
