Function for Auto Populate

[randfan]

Beginner here. I'm looking for a way to auto-populate one cell based
on, or "triggered" by the input from a previous cell (or selection from
a drop down validation list). The "IF" function only seems to work
with numerical values, or only if the "logical test" is a numerical or
single letter symbol. A simple example of what I am looking for is
below:

If I type "Harold Smith" in A1, I want his supervisor, "Scott Jones",
to automatically populate in B2.

I tried to make it happen using the "IF" function:

Logical_test Harold Smith
Value_if_true "Scott Jones"
Value_if_false "None"

I get the "#NAME?" result. Any suggestions would be appreciated.

Tim Hopkins

 

[Ragdyer]

How about posting the *actual* formula that you're using?

 

[Dave Peterson]

=if(a1="harold smith","Scott Jones","None")

If you have lots of names that could go into A1, you might want to create a 2
column table on another sheet with employee in column A and supervisor in column
B.

Then you could use =vlookup() to return the supy's name.

Visit Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
for nice instructions.

 

[Rowan]

If Harold Smith was the only name you were wanting to check in A1 you could
use the formula:

=IF(A1="Harold Smith","Scott Jones","None")

If you have a list of names and supervisors you want to check you are better
off using a Vlookup. On Sheet2 (or rename it as you like) enter a list of the
employees in column A and their respective supervisors in Column B. On your
original sheet in B2 you can then enter the formula

=VLOOKUP(A1,Sheet2!A:B,2,0)

This will work for all the names you have entered onto sheet 2. If you have
names in the drop down validation in A1 that do not appear in your list in
column A on sheet 2 you will get an #N/A error. To get around this you can
modify the above formula to read

=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),"None",VLOOKUP(A1,Sheet2!A:B,2,0))

Hope this helps
Rowan