Auto fill cell depending on what is in neighboring cell. Possibly an IF function.

[fatgeek]

Ok, I'm sure for someone a lot better with Excel than I am this is easy.

I have a spreadsheet where I have employee names in column A, work locations in column B. I would like to have a third column where their supervisor's name is filled depending on the work location in column B.

Basicially I would like:

A	B	C
John	North	SUPV A
Joe	South	SUPV B
Jack	South	SUPV B
Janet	North	SUPV A
Janice	East	SUPV C

<tbody>
</tbody>

And so on and so forth where it would fill column c appropriately based on the contents of the neighboring cell in column B. I do a little coding, so I know I could do this with an if statement, but I don't know how to get that working with Excel.

Thanks in advance

 

[jai9]

Hi,

Try

=IF(B1="East","SUPV C",IF(B1="West","SUPV D",IF(B1="North","SUPV A",IF(B1="South","SUPV B",""))))

Jai

 

[fatgeek]

That did it! Thanks so much!

 

[fatgeek]

Just to help me understand what is going on:

It looks like what you provided was a bunch of nested IF statements, and the syntax is basically:

IF(cell="i",then,else)

Am I reading that correctly?

 

[JMonty]

You are correct. Basically, what he did was a nested "if this location, then this sup, else if this location, then this sup, else...".

I believe a VLOOKUP works and a separate table works better but for simplicity's sake, this is perfect.

 

[SteveK_LA]

Depending upon how many "supervisors" there are, as there are a limit to the number of nested if statements in some versions of excel, then either the If function as mentioned above (or below depending upon how you're reading the thread) or a Vlook up are the two main ways i would suggest. You could even prefix with
If(ISBLANK(B1),"",---- then the VLook Up formula. The If above takes care of this already with the final "", vlook up would tend to return a #N/A if Cell B was blank!