Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to do something which I hope will be reasonably straightforward in Microsoft Excel. I'm using 2010.

I have two columns called Username and Year Group.

Username takes the format of something like, 11rennem01 or 10oxbech1. Year Group is just a single digit figure, one of 5/6/7/8.

In a third column, I would like to display the students' shared drives, which take the following format: \\server\student\IntakeYear\Username`. For example, the aforementioned students' paths will be:

(Year 5) 11rennem01 -> \\server\student\Intake2011\11rennem01
(Year 6) 10oxbech1 -> \\server\student\Intake2010\10oxbech1
(Year 7) 09clarco01 -> \\server\student\Intake2009\09clarco01
(Year 8) 08wilkbe01 -> \\server\student\Intake2008\08wilkbe01

Unfortunately, despite the numbers 11 and 10 at the start of the students' usernames matching up, there's a possibility, with a few students, that the number at the start of their username will be different to their Intake Year. As such, the only reliable value is the Year Group figure in the second column.

How can I go about writing such an IF statement?

Thanks in advance,

share|improve this question
    
Wouldn't 2003 + Year Group work, instead of an if statement? Or is it more complicated than that? A nested if is possible, but the nesting depth is limited to 7 AFAIK, which might be problematic three years from now. –  FrederikVds Jun 12 '12 at 13:51
    
Unfortunately not - I'll update my post so you can see why. Apologies, think I had one of the network paths wrong too. Doh! Fixed now. –  dunc Jun 12 '12 at 13:55

4 Answers 4

up vote 1 down vote accepted

Assuming cell A1=Username, cell B1=Year Group, then in cell C1 you should write:

="\\server\student\Intake"&IF(B1=5;"2011";IF(B1=6;"2010";IF(B1=7;"2009";"2008")))&"\"&A1

and then copy cell C1 into as many cells of row C as you need

share|improve this answer

In column three enter the following code:

="\\server\student\Intake"&2016-B7&"\"&A7

Your output will be:

Username    Year Group  SharedDrive

11rennem01  5          \\server\student\Intake2011\11rennem01
share|improve this answer

change it from IF, which tends to look horrible once you nest a few.

As you have a small start point, I would use CHOOSE. In your case, use
=CHOOSE(A2,"Invalid","Invalid","Invalid","Invalid","2008","2009","2010","2011")
to construct the directory

share|improve this answer

I'd use either the LOOKUP function with a small table mapping Intake Years to share folders (http://support.microsoft.com/kb/324986), or a big IF like this:

=IF(A2 = "Year 5", "Intake2011", IF(A2 = "Year 6", "Intake2010", IF(A2 = "Year 7", "Intake2009", "Intake2008")))

The lookup table solution is better though, in my opinion.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.