# Microsoft Excel Problem

Started by
mgreg
, Jul 20 2006 01:06 PM

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

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

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

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

#### Attached Files

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

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

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.**

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

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.

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

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 matchin 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

### Similar Topics

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

0 members, 0 guests, 0 anonymous users