rotating list
Hi Shannon
See below.
"Leo Heuser" skrev i en meddelelse
...
"Shannon" skrev i en meddelelse
...
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
<Ctrlr)
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
|