paste sequences of different lengths
 

I need to paste sequences of different lengths onto another sheet in such a
way that the sequence will repeat itself.

e.g. Joe Soap works 3 shifts on 3 shifts off, this is shown on the info page
(sheet 1) with headings of: 'day 1' day 2' etc. I want to paste this info
onto a calendar page (sheet 2) so that it repeats itself.

The link/formula needs to be able to adjust to different lengths of
sequences so that if I change Joes shift pattern on the info page the
calendar page will reflect the new shift pattern

Info page
day 1 day 2 day 3 day 4 day 5 day 6 day 7 day 8
Joe Soap 12 12 12 0 0 0
Jim Bean 12 12 12 12 0 0 0
0

Calendar page
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri
Sat Sun
Joe Soap 12 12 12 0 0 0 12 12 12 0
0 0 12 12
Jim Bean 12 12 12 12 0 0 0 0 12 12
12 12 0 0

if the 'working week' is always the same length (eg. 6 days) then this isn't
difficult- you can just link the cells in the calendar to the cells in the
info sheet and they will adjust accordingly.

"Catherine" wrote:

I need to paste sequences of different lengths onto another sheet in such a
way that the sequence will repeat itself.

e.g. Joe Soap works 3 shifts on 3 shifts off, this is shown on the info page
(sheet 1) with headings of: 'day 1' day 2' etc. I want to paste this info
onto a calendar page (sheet 2) so that it repeats itself.

The link/formula needs to be able to adjust to different lengths of
sequences so that if I change Joes shift pattern on the info page the
calendar page will reflect the new shift pattern

Info page
day 1 day 2 day 3 day 4 day 5 day 6 day 7 day 8
Joe Soap 12 12 12 0 0 0
Jim Bean 12 12 12 12 0 0 0
0

Calendar page
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri
Sat Sun
Joe Soap 12 12 12 0 0 0 12 12 12 0
0 0 12 12
Jim Bean 12 12 12 12 0 0 0 0 12 12
12 12 0 0

=IF(MOD(COLUMN(A1)-COLUMN($A$1),6)<3,12,"") for soap

=IF(MOD(COLUMN(A1)-COLUMN($A$1),8)<4,12,"") for bean

or if you had a helper cell before day one:

EXample
col A day 1 day 2 day 3 day 4 day 5 day 6 day
7 day 8
3 Joe Soap 12 12 12 0 0 0

=IF(MOD(COLUMN(A1)-COLUMN($A$1),a2*2)<a2,12,"")

could work generically for all rotations


Lance

"Catherine" wrote:

I need to paste sequences of different lengths onto another sheet in such a
way that the sequence will repeat itself.

e.g. Joe Soap works 3 shifts on 3 shifts off, this is shown on the info page
(sheet 1) with headings of: 'day 1' day 2' etc. I want to paste this info
onto a calendar page (sheet 2) so that it repeats itself.

The link/formula needs to be able to adjust to different lengths of
sequences so that if I change Joes shift pattern on the info page the
calendar page will reflect the new shift pattern

Info page
day 1 day 2 day 3 day 4 day 5 day 6 day 7 day 8
Joe Soap 12 12 12 0 0 0
Jim Bean 12 12 12 12 0 0 0
0

Calendar page
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri
Sat Sun
Joe Soap 12 12 12 0 0 0 12 12 12 0
0 0 12 12
Jim Bean 12 12 12 12 0 0 0 0 12 12
12 12 0 0