Help needed

  • Thread starter Thread starter Roy
  • Start date Start date
R

Roy

At work we run 4 production Teams; Team 1 thru Team 4. Each Team is
assigned a production schedule every week. There are a total of 23 possible
production schedules, each one different.
Every week the various production schedule number (1 - 23) is recorded in a
column under the Team's number. The sequence of the schedules is erratic,
that is the same team could have the same schedule in two consecutive weeks;
or could go from schedule 8 to schedule 15. At the leftmost column is the
date.

What I need to do is, on another worksheet, enter a row of numbers 1 - 23.
Under each number I need a formula that will look at all the various
production schedules, in total, and tell me how many weeks it's been since
that particular schedule was run. An example is below. It does not need to
be by team, just the weeks since a particular schedule was run.

1 2 3 4
10/04/03 15 22 14 9
10/11/03 5 11 22 17
10/18/03 4 6 21 23
10/25/03 7 12 21 19


Thanks in anticipation
 
Roy
I have a "not very elegant" solution.

I have done it all on the one worksheet, so you will have to re-arrange it.

I have your table in cell A1 to E5
In H2 to H23 I have the numbers 1 to 23 (the numbers of the schedules)
Cell I2 the formula is =IF(J2+K2+L2+M2=0,"",J2+K2+L2+M2)
Cell J2
=IF(ISNA(MATCH($H2,B$2:B$5,0)),0,MATCH($H2,B$2:B$5,0))
Cell K2
=IF(ISNA(MATCH($H3,C$2:C$5,0)),0,MATCH($H3,C$2:C$5,0))
Cell L2
=IF(ISNA(MATCH($H2,D$2:D$5,0)),0,MATCH($H2,D$2:D$5,0))
Cell M2
=IF(ISNA(MATCH($H2,E$2:E$5,0)),0,MATCH($H2,E$2:E$5,0))

You can then copy (drag) these five formulas down to fill all the cells down
to line 23
If this looks untidy hide columns J, K, L, & M.

Your results should be in columns H & I

I think this is what you wanted

Alan
 
Thanks Alan

I've done as you've suggested but it doesn't seen to return the correct
number of weeks since a particular schedule run. As an example in cell B2,
I've entered 6 - but the value returned in cell I7 is 2. Where I would have
expected 3. Since it's been 3 weeks since last produced.
 
Sorry Roy

I am struggling a bit myself.

The formulas I gave you only covered the information you gave me.
I suggest you change the formulas in the following way.
The bit of the table it looks at is the

In row K C$2:C$5
In row L D$2:D$5
In row M E$2:E$5
In Row N F$2:F$5

All you have to do is replace the all the 5s with 52 if you want it to last
for a year, or any other figure.

Once you have replaced the 5s in the top row drag them or copy them down to
the other cells.

One condition that the system won't work is if two teams do the same
schedule in the same week as the formulas in column I expect only one number
in each 4 column row. Is this a problem?

Alan
 
Back
Top