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

fatgeek

New Member
Joined
Mar 15, 2013
Messages
4
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:

ABC
JohnNorthSUPV A
JoeSouthSUPV B
JackSouthSUPV B
JanetNorthSUPV A
JaniceEastSUPV 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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jai9

Active Member
Joined
Jul 20, 2012
Messages
325
Hi,

Try

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

Jai
 

fatgeek

New Member
Joined
Mar 15, 2013
Messages
4
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

Board Regular
Joined
May 28, 2012
Messages
185
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

New Member
Joined
Nov 9, 2011
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,365
Messages
5,444,032
Members
405,262
Latest member
palash1010

This Week's Hot Topics

Top