Trying to create a standby list

[oogles36]

I have a list of 12 teams and 12 assignments, we will just use letters for their names and numbers for the assignment

A
B
C
D
E
F
G
H
I
J
K
L

I want to give each letter an assignment and have it rotate each week. Lets say Assignment 1 is the worst and 12 is the best. I want whomever is 1 to get 12 next week.

I want to create a list of which team is in charge each week and rank them. So the first week of the year (January 2 - January 8) Team A is number 1, followed by B, C etc.
The next week I would want the order to be flipped so that the top 3 end up being the bottom 3 of next week.

I want to create this spreadsheet so it rotates the entire year. and every 12 weeks your team should be number 1...

 

[oogles36]

After searching through the forums this will be similar to an on call list that rotates every 7 days. I can't figure out a way to have it be fair and equitable in some sort of a rotation.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
2	A	1	12	11	10	9	8	7	6	5	4	3	2	1
3	B	2	1	12	11	10	9	8	7	6	5	4	3	2
4	C	3	2	1	12	11	10	9	8	7	6	5	4	3
5	D	4	3	2	1	12	11	10	9	8	7	6	5	4
6	E	5	4	3	2	1	12	11	10	9	8	7	6	5
7	F	6	5	4	3	2	1	12	11	10	9	8	7	6
8	G	7	6	5	4	3	2	1	12	11	10	9	8	7
9	H	8	7	6	5	4	3	2	1	12	11	10	9	8
10	I	9	8	7	6	5	4	3	2	1	12	11	10	9
11	J	10	9	8	7	6	5	4	3	2	1	12	11	10
12	K	11	10	9	8	7	6	5	4	3	2	1	12	11
13	L	12	11	10	9	8	7	6	5	4	3	2	1	12
Sheet6

Cell Formulas
Range	Formula
B2:N13	B2	=MOD(ROWS(B$2:B2)-COLUMNS($B2:B2),12)+1

 

[oogles36]

I tried to do something like that also, but I need to find out a way to get the top 3 or 4 to be the bottom 3 or 4 the next week.

so if week 1 is A B C D
Week 2 should NOT have A B C D in the top 4.
Ideally, A should be 12, B should be 11, C should be 10 and D should be 9.

Does that make sense? I just dont know if there is a way to do that math wise... and thank you for the warm welcome.

 

[Fluff]

Does that make sense?

Not really, you would need to show exactly what each team should be for each week.

 

[oogles36]

Not really, you would need to show exactly what each team should be for each week.

Trying to get something like this where you can see ABC move to the bottom of week 2. I just cant get the right rotation so that the same people aren't at the top of the list.

 

[Fluff]

But what happens the following weeks? We would need to see where each team should be each week to try & figure something out (assuming it's possible)

 

[oogles36]

Sorry for the delay, I was able to do it manually for 12 different weeks and then just copied those 12 for the rest of the year. I ran into some hiccups where the 2nd week 2 ended up being the 3rd for week 3 and that pattern kept repeating. So I just swapped team 3 with team 7 each week. I was hoping to be able to use a formula to do it all on its own but was spending numerous hours trying to get a formula to work when it only took me 45 minutes to do it all manually lol.

Appreciate the help.

 

[Fluff]

Glad it's sorted & thanks for letting us know.