Hi everyone,
I don't have a spreadsheet to post at present, but I'll try to explain what I am looking for.
I have a spreadsheet row 1-3are dates and days of the week
Column A is the names of the staff, ( I have 2 different groups, each a separate but identical sheet)
From cell D5:HJ11 is basically a roster pattern from left to right with 1 meaning work and F meaning day off.
The staff are in their current fixed pattern of 5 days on and 3 days off. This pattern is fixed by Union rules, but the pattern can be changed by agreement.
Row 14 is staff available to work =countif(D5:D11),"1". For column D. The formula has been copied across the sheet.
These cells also have a conditional format which change colour if equal to or below the cell below which is staff required.
Row 17 is staff required
Cell B18 is the average staff number available.
So this is the cell that I want to have the highest with solver.
Variable cells are cell D5:HJ11
Constraints
D14:HJ14 int integer
D14:HJ14 must be greater or equal to row 17.( the staff requirement is the same every day of the year.
But if I run this, I assume that solver will just change all of the values in D5:HJ11 to the most efficient value, but the pattern will be random.
So how can I create a constraint that each staff member has 11111FFF11111FFF for example but it can change what day this pattern starts?
I should have excel 2007 installed by tomorrow, and I will have solver installed then, so I can get working and experimenting.
My current version is 2000, and I cannot find the disc to install solver!! It's been a few years since I installed it!
Any help would be appreciated. I might have an old file with the pattern, but it will be 2000 if that's ok.
Many thanks
Bookmarks