Stock reallocation between stores

[Leo Milano]

Hi there,
I have a logic problem. I want to reallocate our stock in 5 stores so we have a close balance between them. I want to make a spreadsheet that instructs automatically which products, from where, to where, and how many, so I can have it as evenly distribuited as possible.

I have done some file work and some code, but i have no ideia on how to write this logic.

I'm linking the file, it stars by showing the total of all stores. Clicking the first button, it prints lists for the 5 stores for product count

The second button shows 5 sheets with each store count, the last button shows new 5 sheets where it should be printed the diretions. From where, what, how many, and to where.

I've been trying to come up with something, but i get quite lost to be honest.
Hope someone can help me.

 

[vletm]

Leo Milano - five short questions...
Which sheets would be those five stocks?
How do You get or update each stocks information?
Which sheet You would like to get something?
Do You need all those 11 sheets?
Is that only possible layout?

 

[Leo Milano]

How do You get or update each stocks information? And Which sheets would be those five stocks?

Its a manual process. First, in the "total" sheet I set which label and products we are going to count (we have 40k+ different products, so we count label by label, as an example in this saved file, we are counting a specific brand of yarn from Círculo, the slots were filled with colors still available for purchase). This way I create a list that each stock manager can count his own stock.

Sheets 2 to 6 have the list layout for printing, and there I can update each sheet as they come back filled.


Which sheet You would like to get something?
I'd like to write a code that print into sheets 7 to 11 instructions for the stock managers on where they should send which and how many products
I have some failed code there

Do You need all those 11 sheets? And Is that only possible layout?
Definitely no. That was the best I could come with, having in mind layouts to print on paper. I see it has many flaws, would love suggestions

 

[vletm]

Leo Milano
... hmm?
You will need at least one sheet - okay?
I 'totally hide' others ... except one!
and left there 'total'-sheet.

The 1st sample...
1) Fill 'light green values'
2) Press [ Do It ]
3) You'll see, what to do for You stocks
4) ... and the new stock values

Many things will depend, how do You can get/fill those 'Cores'? and values ...
... with as less as possible 'manual process'

Ideas? Questions?

 

[vletm]

Leo Milano
... there is missing one option as You might notice.
You would get it with next version.

 

[Leo Milano]

Dear vletm,
it took me some time to finally get to the computer and take a look at your version. OMG i'm baffled. This miles and miles further. I really like how you dealed all info in one sheet. That's amazing. I'll take some time so i can fully understand the code, and I'm sure I'll learn a lot easily by it.
As of right now, I don't even know what could be this mission option.
I have some ideas on creating a DB with all labels and brands, so it would get less manual. But right now I'll spend some time taking a look a this beauty.
Thank you so much!

 

[vletm]

Leo Milano
You would test this version ... it'll give more smooth values.