auto change line

[jinvictor]

what i want is:

a1-a48 different names
b1-n1 different shifts, etc: morning shift, afternoon shift, night shift, rdo
b2-n2 different shifts again,different as b1-n1
all the way to line 48

every saturday fornight week, we work drop 1 line, say if my name in a1 this fornight, saturday 2 weeks later, my name will be on line 2(b2-n2). and person in a48 will be in a1.
how can i do it so every 2nd saturday when i open the workbook, it automaticaly change to new line which i suppose to be.

thanx

[Zygan]

if i understand correctly the lines B2 - N48 stay the same and the A column just moves down one and the bottom one goes to the top ?
If so put this code in and all you will have to do is run it every saturday


Sub name_movement()

Range("A48").Select
Application.CutCopyMode = False
Selection.Cut
Range("A49").Select
ActiveSheet.Paste
Range("A1:A47").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("A49").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
End Sub
basically what it does is select a48 and puts it into a49 (so as you dont lose it) the a1:a47 cuts this info and paste into a2:a49 then puts the original a48 (now in a49) into cell a1 and all done

p.s did not really grasp your idea around the spreadshees maybe if this dont help you could explain it further

[jinvictor]

it is very helpful, but another problem is how can i make it do that every secound saturday?

thanx for your time.

[Max]

Here's a play using non-array formulas ..

Sample construct available at:
http://www.savefile.com/files/5315921
48 names - Fortnightly Saturdays rotation schedule

In a sheet: Names, list the 48 names in A1:A48
Put in A49: =A1
Copy A49 down to A96 to repeat the namelist

In another sheet: X

Fill the numbers 1 - 48 across in A1:AV1
(these numbers will be the 48 roster #s)

Put in A2: =OFFSET(INDIRECT("'Names'!A"&ROW(A1)),COLUMN(A1)-1,)
Copy A2 across 48 cols to AV2, fill down to AV49 to populate the schedule
A2:AV49 will return the required 48 rotational rosters / schedules

In a new sheet: Y,

Put in A1: 10-Jun-2006
Put in A2: 24-Jun-2006 (the next fortnight Sat)

Select A1:A2, fill down to say, A96
to fill 2 cycle's worth of fortnight Saturdays till 30-Jan-2010

Put in B1: =MOD(ROW(A1)-1,48)+1
Copy B1 down to B96
This quickly numbers 2 cycles of 1-48, viz.:
1-48 in B1:B48, then 1-48 again in B2:B96

Then, in the sheet with your 48 horizontal "shifts" listed in B1:N48
(assume this sheet is named as: Z)

Insert a new row1, then put in the new A1:
=IF(ISNUMBER(MATCH(TODAY(),Y!A:A,0)),OFFSET(X!A:A,,MATCH(VLOOKUP(TODAY(),Y!A:B,2,0),X!$1:$1,0)-1),"")
Copy A1 down to A49

A2:A49 returns the required roster from X
depending on the current date
(i.e. the Saturday's date which matches the current date)
[A1 returns the roster #]

And if the current date doesn't match with the Sat dates listed in Y's col A,
then col A will appear "blank".

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jinvictor" wrote:
>
> what i want is:
>
> a1-a48 different names
> b1-n1 different shifts, etc: morning shift, afternoon shift,
> night shift, rdo
> b2-n2 different shifts again,different as b1-n1
> all the way to line 48
>
> every saturday fornight week, we work drop 1 line, say if my name in a1
> this fornight, saturday 2 weeks later, my name will be on line 2(b2-n2).
> and person in a48 will be in a1.
> how can i do it so every 2nd saturday when i open the workbook, it
> automaticaly change to new line which i suppose to be.
>
> thanx
>
>
> --
> jinvictor
> ------------------------------------------------------------------------
> jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
> View this thread: http://www.excelforum.com/showthread...hreadid=548256

[Max]

Typo in line:
> 1-48 in B1:B48, then 1-48 again in B2:B96

should read as:
> 1-48 in B1:B48, then 1-48 again in B49:B96

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---