Item selection problems

[sentinela]

Hello everyone.

I am stuck on a problem in a spread sheet.

I have 6 groups and I have bought different color, and size t-shirts for each group. Now, in this spread sheet I am trying see what color needs to go to each group. Different groups can have same colors, but a group must have only one color.

I have not been able to calculate the left over inventory, because two different groups can have same colors. When that happens, I cannot figure a way to calculate that.

I have attached my spreadsheet there if anyone has any idea, it would be appreciated.

Thanks!

sentinela

[Moriarti]

Now, in this spread sheet I am trying see what color needs to go to each group.

I don't understand how one should decide what color goes to which group?There should be a condition or i'm missing something?Or it's completely random as long,as there's enough t-shirt's of one color for that particular group?

[sentinela]

Moriarti,
Thanks for the reply...
Your last guess is correct. As long as there is enough shirts to one group, then, they can have it.
This is my main reason, I do not want to end up with only x-large shirts of one color, because I can never make a set. I am trying to end up either with completely 0 or a nice mixture.

I appreciated all your help!

Sentinela

[Moriarti]

When then it should depend on term "nice mixture"
If it means as possible equal or close to that quantity of various size and color shirts,then you should make array,then find the biggest element of that row(in your case 30(coll - small,row - yellow),then check the array of shirts needed also from the biggest memeber in that row(in your case 7) and substract 1 from both numbers,this is because you have the biggest demand of that size shirts(color isn't an issue,so size is what important),then again check both arrays to see if situation didn't changed,if those numbers ar still biggest(it could be also equal to any number in row,if you subtract 1 from that number,you would have to do that any way with another number which is same as the first one you found).
Sorry,i've to go home from work,i'll check this when i'm back at my place

[sentinela]

Thanks Moriati,

I am not sure if I understand correctly, I look forward to your work when you get back to your place.
I need something that is automatic, when I pick yellow, I need to see the inventory left over to the yellow shirt, or the orange. I may change around a little bit, and pick navy blue to a group and then change to orange.

I hope I am making sense with my problems.

Thanks!

[Alf]

Perhaps this is of help to you?

A world of warning. This solution is based on the layout of your original file so it will work as long as you don't changes the ranges for "T-Shirts Inventory",
"T-Shirt Needs" and "T-Shirts Final Inventory".

Alf

[sentinela]

Alf,
Thanks for the reply.

I have downloaded and tried to use the spreadsheet, but it doesn't change when I make my changes.
Is that something that I need to do, that perhaps I am not aware of it?

Thanks for all the help!

[Alf]

Strange, I've just downloaded the file and tested it. On my pc it works.

Have you set your macro security too high perhaps? Take a look at

Tools -> Macro -> Security Choose "Medium" for "Security Level".

This macro works for Excel 2003 and probably Excel 2007. Don't know about Excel 2000.

Since this is a "worksheet change" macro it will only run on sheet1 the way it's set up at the moment.

Could you try to right-click on tab for Sheet1 and go to "View Code". You should see the code as shovn below. If not, copy code and paste it.

Code as follows:

Please Login or Register  to view this content.

Hope this helps

Alf

[sentinela]

Alf,
Yes, it works. I thought I had enabled, but, for some reason didn't save my settings. But now it is working.
Thanks for the help!

[Alf]

Glad to be of help.


At the moment the macro only runs when you change the colour of the t-shirts (Range F12:F17).

If you want it to change the t-shirt needs for the different groups and fire the macro when you do so change the line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf