Mar 23 2023 11:19 AM
Hi,
Please assist me with a formula. I have explained what I want in the file.
Mar 23 2023 01:29 PM
Are you open to a different approach?
I think, to be more specific, you'd be better served by a single transactional table that just begins with a row of opening inventory levels in the two spots. (That's what you have now as a separate table, which I'm suggesting is adding an unnecessary complication.)
With a single transactional table, you can handle transfers, incoming shipments, outgoing, and resulting balances, all in one single database.
Mar 23 2023 07:12 PM
Mar 24 2023 06:48 AM
Could you kindly edit the workbook and show me with one table.
I can do that. First let me ask what rules or assumptions have you built into this? For example:
Finally, are there any other factors that I've not asked about but that you take for granted?
Mar 24 2023 08:42 AM
Could you kindly edit the workbook and show me with one table.
I can do that. First let me ask what rules or assumptions have you built into this? For example:
Finally, are there any other factors that I've not asked about but that you take for granted? For me, what is important is to know the running totals of each items per store and the quantity that needs to be requested from the Main store by the storekeepers.
I hope to have shed more light.
Mar 24 2023 09:28 AM
I hope to have shed more light.
You did for sure. But your responses also raised a few more questions. (In order to have a really robust system/spreadsheet, it is important to spell out all the details.)
That's all for now. Thanks.
Mar 24 2023 10:06 AM
You did for sure. But your responses also raised a few more questions. (In order to have a really robust system/spreadsheet, it is important to spell out all the details.)
Item Code | Item Name | Transfer Size |
C3434 | 30 Col | 1000ml |
C3069 | 1.8 EC | 1000ml |
C3990 | DS 4 EC | 5000gms |
C3264 | R Gold | 2500gms |
C3395 | 240SC R | 1000gms |
C3241 | 50 WG | 1000ml |
Thank you and hoping not to have thrown a spanner in the works.
Mar 24 2023 12:45 PM
Mar 25 2023 10:37 AM
Mar 26 2023 10:29 AM
This has turned out to be a bit more complicated (for me) than I'd anticipated. Part of it is that my on-line connection is giving me trouble this morning.
Take a look at what's here and see if it does at least some of what you're wanting to do. I know the formulas can be made more elegant (using LET, for example), but I wanted first to check with you to see if it was even going in the right direction.
Based on your original sheet, the formulas you'd written there, it's clear that you're pretty adept with some pretty complicated formulas, and with data validation and other semi-advanced features. Maybe you can just build on what I've started here, but by all means come back with any questions. I think my main point was that you should be able to have it all managed with one main "transactional" table. There is a behind-the-scenese table with info on each product, but other than that.....from Initialization to all the other transactions that take place, it can all be handled on one table, with running totals up-to-date.
Mar 26 2023 03:13 PM - edited Mar 26 2023 06:30 PM
A much improved version (in that the formulas now take advantage of the LET function, to be more readable). And they're more consistent. there some documentation of the formulas, but if you have questions about them, feel free to come back with those questions.
Mar 27 2023 04:29 AM
thank you for the file and introducing me to the LET function.
Would it be possible to add the "refill" quantities suggested in column K ( through a formula) to the running totals per store ( add to Column 1 and transfer from j) without the need to manually do this. When I have 15 different items that need a refill, I will be required to manually add 30 new rows to update the stock.
Mar 27 2023 12:00 PM
If LET is new to you, I recommend some research. Here are several resources to learn more about it.
Would it be possible to add the "refill" quantities suggested in column K ( through a formula) to the running totals per store ( add to Column 1 and transfer from j) without the need to manually do this. When I have 15 different items that need a refill, I will be required to manually add 30 new rows to update the stock.
A formula to do that would be tricky, if possible; it would surely be possible via a macro (which I don't write) BUT, anyway, as your consultant on this (for the time being at any rate), I would strongly advise against trying to accomplish that anyway.
Of course, I'm not really your consultant, and I'm certainly not there on the scene using the spreadsheet, but those are my thoughts. I would definitely recommend living with it as it is for a while, though, to see if there are other refinements you'd want to see.