Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Microsoft Excel Problem

### #1 mgreg Posted 20 July 2006 - 01:06 PM

mgreg

New Member

• Member
• 2 posts
Can anybody help me. I'm trying to put together a spreadsheet using the 'IF' procedure. The only problem is that I want a list of names to rotate according to a week number but I can only nest the formula 7 times and its a 12 week cycle. I hope that makes sense..I'm just about as frustrated as you can get after trying to solve this for the last 4 hours. ANY HELP GLADLY ACCEPTED.
• 0

### #2 dsm Posted 21 July 2006 - 04:59 AM

dsm

Member

• Member
• 98 posts
mgreg,
Microsoft Excel is limited to 7 nests, so you are unlikely to acheive the desired result using an if statement.
Fortunately there are other methods.

A simple solution is to use the Vlookup function. You will need the following;
-A week number (1 - 12) is assigned to each line in your spreadsheet
-A small table with 1 -12 in the first column and the assigned name in the second column
-A formula which links this data

=VLOOKUP(B17,\$M\$11:\$N\$23,2,FALSE)

in this example;
B17 contains the week number you have assigned to each line
\$M\$11:\$N\$23 is the table showing the name for each week number
2 tells the formula to return the value from the second column
FALSE tells the formula to find an exact match

I have attached an example spreadsheet demonstrating the solution.

Hope this Helps

DSM

• 0

### #3 mgreg Posted 21 July 2006 - 10:19 AM

mgreg

New Member

• Topic Starter
• Member
• 2 posts
Hi

Firstly, I am really learning as I go with this so this may be me being thick. My problem is that with that solution the name stays the same. what I want is the names to rotate as the week changes, eg. week 1 bill is first, week 2 bill is second, week 3 bill is third etc. I dont see how this solution does that. Again sorry for being thick if it does I just don't see how.

Mgreg

Edited by mgreg, 21 July 2006 - 10:22 AM.

• 0

### #4 dsm Posted 21 July 2006 - 05:14 PM

dsm

Member

• Member
• 98 posts
Mgreg,
I didn't fully understand what the end result should look like, and i'm not sure I do yet from your description.

Assuming you need 3 or more people rostered each week, develop the lookup table as follows;
week1 1st is Alan, 2nd is Bill, 3rd is Charlie, ...
week2 1st is Bill, 2nd is Charlie, 3rd is Doug, ...
...
week12 1st is Jimmy, 2nd is Zena, ...

the lookup table now has 12 rows of data and as many columns as you need people rostered + the first column with the week number.

back to the original data.
you can repeat the formula in multiple columns as long as you point to the correct cells.

=VLOOKUP(B17,\$M\$11:\$N\$23,2,FALSE)
in this example;
B17 contains the week number you have assigned to each line
\$M\$11:\$N\$23 is the table showing the name for each week number
2 tells the formula to return the value from the second column
FALSE tells the formula to find an exact match

To get the 2nd rostered person change the 2 before the false to 3, to get the 3rd rostered person change it to a 4, ...

If this doesn't solve your problem then I have not understood what you need.
In this case zip and attach an example spreadsheet showing about 36 weeks and what it should look like.

Regards
DSM
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users