Calc, inventory and stock management

[marko2002]

Hopefully I can explain this logically, bear with me

Currently, I have a sales sheet where I enter the details of our sales.

DATE | PRODUCT SOLD | QTY SOLD | CUSTOMER DETAILS

The “Product Sold” field is simply copied and pasted from the previous line and I have this field as a dropdown which contains all our products.

What I would love to have is this …

DATE | PRODUCT SOLD | QTY AVAILABLE | QTY SOLD | CUSTOMER DETAILS

I understand I’d likely need to have another sheet with all the products listed there along with the qty’s available, etc but I can’t get my head around how I would get what I wanted without a massive formula that would have to be applied to each cell on the “sales” sheet which could be quite unworkable.

Essentially I’m looking for a way to have another sheet, like a stock sheet, with the products and qty’s available and have those qty’s shown on the sales sheet once I selected the product from the dropdown we have there currently. Obviously this would also need to be adjusted automatically too, so when I select PRODUCT1 on the sales sheet and enter QTY SOLD 3, if the QTY AVAILABLE was previously 4 from the stock sheet this now show QTY AVAILABLE 1 on both the sales sheet and the stock sheet.

Hope that all makes sense
All the best
Marko

[newbie-02]

hello @marko2002,
it makes sense,
but needs to find a balance between your ideas and the capabilities of a spreadsheet,
just ‘brain-jogging’ for me … a sheet has problems with a reference value - your stock - using as basis for a calculation, and! then changing it, that would require a marker ‘stock reduction already done’ to avoid multipe subtractions on touching / recalculating the formula … touches ‘circular references’ and ‘iterations’ … not so easy … will stay error prone anyway …
other idea - if you dont want to invest in a special program - one sheet / range with ‘inventory stock’ as start value, one sheet / range with your sales list, then you can calculate the available items with a ‘simple’ sumproduct-formula and add to either or both lists,
if you add ‘negative sells’ for purchases / stock additions you are near to a professional ressource management
ok?

[marko2002]

Your reply makes perfect sense, and I do think I could be at the point of our requiring to look towards a dedicated invenotry/stock/accounting program and I have dabbled a little before with some but none offer the flexibility or ease of use for us, it’s purely a learning curve I guess, but we’ve always reverted back to the spreadsheet because it’s what we are familiar and comfortable, but yes I think we will continue with the sheet as it is for now but take a more serious look into a dedicated software program, at least this can be automated to do what we would require without the headache of maintaining the spreadsheet.

[Villeroy]

https://ask.libreoffice.org/uploads/short-url/6UJSfWwVqoHijIDIdr1FBQWdc1k.odb
Input and edit in forms, balance in reports.

[newbie-02]

hello @marko2002:

just to not to be the one who pushed you away from spreadsheets, and as i like them handy and flexible as well,

attached a little sample with your wishes circled in green - click to open -

it’ not a perfect solution, but a ‘proof of concept’, just tailor to your needs …

[ it’s a try of a ‘kiss’ answer, ‘keep it simple and stupid’, i’d like to learn if there are simpler solutions … ]

reg. b.

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

[marko2002]

That is really useful, thanks for that, I will indeed give this a little mess around with and see if I can have it fit my purpose
Marko

[newbie-02]

thanks for liking, just two additonal suggestions:
(i’m not too bad in thinking, sometimes a little slow … )

• implement actual work (new sales and if needed totals) on top of the sheet, it’s faster ‘at hand’ and keeps fixed places for referencing, and let the historical data ‘flow out’ to the bottom of the sheet (can be realised by a macro to add a new row for new sales …),
  (such is also ‘nice’ for calculations of e.g. running totals, since calc works ‘bottom up’ (and then ‘right->left’), for the same purpose in smaller range it may be better to place input values on the right and calculated results left of it … ),
• let ‘consolidated data’ flow out to the history, not formulas, thus avoid performance penalties from formulas recalculated ever and ever and ever again, (can be realised with a macro which copies an entry and replaces it with pure numbers / strings by ‘paste special’ in the same place with ‘formulas’ unchecked),
  happy hacking …

[Upow]

Hello,

I’ve been looking for a solution similar to what you posted. I’m looking for clarification on a couple things:

How do you generate the drop down lists?

In your vlookup formula, what does “stock” refer to?

Thank you