Forum Discussion
Formula to add to running totals
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.
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.
- For one thing, the Transfer transaction takes care of both column I and J, adding to the one, subtracting from the other, already, so even in the scenario you describe, you'd only have to add 15 new rows, not thirty. (Does it happen that often that there are multiple re-orders necessary in the same day?!)
- More importantly, making it "automatic" would lead to you (or the human beings responsible for these transactions) becoming being more passive, less engaged in managing the situation. One of the reasons I wrote this as I did, just highlighting the need for a refill after a threshold has been crossed, is to call attention to a need, to prompt the human user to take responsibility for a decision or two (or more):
- Do we need to start stocking larger quantities of this product in Storekeeper? (i.e., change the "minimum")?
- Do we need more this time--seasonal requirement perhaps--than the normal, but can leave the minimum as is?
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.
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.