rotating list

[Guest]

I have a rotation list that I would like to have automated. I work a 7 day
rotation.
a1, a4, a7, needs to rotate. a2,a3,a5,a6,a8,a9,a10 move down one spot

a1- Jill amy Andy
a2 Mary Bj Adam
a3 Jean Mary Bj
a4- Andy Jill Amy
a5 Lee Jean Mary
a6 Jim Lee Jean
a7- Amy Andy Jill
a8 Lori Jim Lee
a9 Adam Lori Jim
a10 BJ Adam Lori

I am just learning functions. Is it possiable to make this automated??
All replies are appreciated. Thanks.

 

[Leo Heuser]

I have a rotation list that I would like to have automated. I work a 7 day
rotation.
a1, a4, a7, needs to rotate. a2,a3,a5,a6,a8,a9,a10 move down one spot

a1- Jill amy Andy
a2 Mary Bj Adam
a3 Jean Mary Bj
a4- Andy Jill Amy
a5 Lee Jean Mary
a6 Jim Lee Jean
a7- Amy Andy Jill
a8 Lori Jim Lee
a9 Adam Lori Jim
a10 BJ Adam Lori

I am just learning functions. Is it possiable to make this automated??
All replies are appreciated. Thanks.

Hi Shannon

Here's one way to do it with a VBA subroutine.

1. Go to the VBA editor with <Alt><F11>
2. In the project window, doubleclick your project.
(if the window is not visible, you can get it with
<Ctrl>r)
3. Choose the menu Insert > Module
4. Copy the code below and paste it into
the righthand window.
5. Alter Set OrgList to reflect your setup
6. Return to the sheet with <Alt><F11>
7. Make a "Button" from the "Forms" commandbar
(right click a bar and choose "Forms")
8. Let the button call the macro "NewList"

Each time you press the button a new list is created
in A1:A10. Pressing the button 21 times, will bring you
to the list displayed, when you started pressing.

------------------------------------------------------

Option Explicit
Option Base 1

Sub NewList()
'Leo Heuser, 26-8-2006
Dim Counter As Long
Dim NewListValue() As Variant
Dim OrgList As Range
Dim OrgListValue As Variant
Dim Placement As Variant

Set OrgList = Worksheets("Sheet1").Range("A1:A10")

Placement = Array(4, 3, 5, 7, 6, 8, 1, 9, 10, 2)

OrgListValue = OrgList.Value

ReDim NewListValue(1 To UBound(OrgListValue, 1), 1 To 1)

For Counter = 1 To UBound(OrgListValue, 1)
NewListValue(Placement(Counter), 1) = _
OrgList(Counter, 1)
Next Counter

OrgList.Value = NewListValue

End Sub

 

[Leo Heuser]

Hi Shannon

See below.

Hi Shannon

Here's one way to do it with a VBA subroutine.

1. Go to the VBA editor with <Alt><F11>
2. In the project window, doubleclick your project.
(if the window is not visible, you can get it with
<Ctrl>r)
3. Choose the menu Insert > Module
4. Copy the code below and paste it into
the righthand window.
5. Alter Set OrgList to reflect your setup
6. Return to the sheet with <Alt><F11>
7. Make a "Button" from the "Forms" commandbar
(right click a bar and choose "Forms")
8. Let the button call the macro "NewList"

Each time you press the button a new list is created
in A1:A10. Pressing the button 21 times, will bring you
to the list displayed, when you started pressing.

------------------------------------------------------

Option Explicit
Option Base 1

Sub NewList()
'Leo Heuser, 26-8-2006
Dim Counter As Long
Dim NewListValue() As Variant
Dim OrgList As Range
Dim OrgListValue As Variant
Dim Placement As Variant

Set OrgList = Worksheets("Sheet1").Range("A1:A10")

Placement = Array(4, 3, 5, 7, 6, 8, 1, 9, 10, 2)

OrgListValue = OrgList.Value

ReDim NewListValue(1 To UBound(OrgListValue, 1), 1 To 1)

For Counter = 1 To UBound(OrgListValue, 1)
NewListValue(Placement(Counter), 1) = _
OrgList(Counter, 1)
Next Counter

OrgList.Value = NewListValue

End Sub

-------------------------------------------------------------

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi again

It just occured to me, that maybe you wanted all seven
lists at the same time in columns next to each other.

Here's one way to do it (No VBA):

Assuming original list in A1:A10.

1. In B1 enter this formula:
=INDEX(A$1:A$10, MATCH(ROW()-ROW($B$1)+1,{4,3,5,7,6,8,1,9,10,2},0))
(Please notice the use of mixed absolute ($) and relative (no $)
references in A$1:A$10)
2. Copy B1 to C1:H1 with the fill handler (the little square
in the lower right corner of the cell)
3. Copy B1:H1 to B10:H10

Regards
Leo Heuser