Forum Discussion
Solved
Lookup Rotating List from week number
Hi all Hope you can help? This one is probably simple but is bamboozling me..... I have four names: Jane Bob John Martin I need a non VBA method to display based on the week number ...
matt0020190 i updated my sample sheet to have a ThisWk cell and a YouPick cell and the corresponding names. i used the exact same formula as B2 but instead of the array $A$2:$A$53 I just point to the WkNumber of interest. Note: that formula is 'overly complicated' in that it doesn't assume how many people are in that list.
Thanks for the reply. Makes sense. A little confused on how to apply to the following though.
Suppose this is my list....
Index ------Name-------WK
0 ---------- Bob----------44
1-----------Jane----------45
2-----------John---------46
3----------Martin--------47
How would I put this in a formula to display as follows (based on the taking in turns theory)
A1 B1
Week 45 is: Jane
A1 B1
Week 48 is: Bob
In simple terms, I know what you mean but cant translate it into my exact purpose. I want to specify the start week number for each name in the above list. Then I want a dynamic cell that will change the name based on the current week number following the pattern of taking in turns all throughout the year.
Can you help me please get over the line?
Many thanks again!
Suppose this is my list....
Index ------Name-------WK
0 ---------- Bob----------44
1-----------Jane----------45
2-----------John---------46
3----------Martin--------47
How would I put this in a formula to display as follows (based on the taking in turns theory)
A1 B1
Week 45 is: Jane
A1 B1
Week 48 is: Bob
In simple terms, I know what you mean but cant translate it into my exact purpose. I want to specify the start week number for each name in the above list. Then I want a dynamic cell that will change the name based on the current week number following the pattern of taking in turns all throughout the year.
Can you help me please get over the line?
Many thanks again!
You would need to just change the order in which the names are listed in your formula. The formula mtarler and Riny provided go through the listed names on a rolling basis, none are repeated twice in a row and each week steps through the list of names from "John" > "Martin" > "Jane" >"Bob" and back again. If you want to change the specific way these appear or force a name to start on a certain week, just change the order in which they're listed.