Return a Title based on a name

[AccAdmin]

I have a proposal sheet used by multiple users, and I would like the cell I
have dedicated to the title of the individual proposing the work to be filled
out automatically once they have placed there name in the cell dedicated for
a signature. Any and all help would be greatly appreciated.

 

[Reitanos]

Are you suggesting that if someone types a name into one cell, you
would like their title to appear in another cell?

You could do this with a lookup (eg VLOOKUP). You might also want to
consider using a drop-down and allowing them to select their name to
avoid typos that could mess up the result of the lookup.

 

[AccAdmin]

I considered using a drop down but changed my mind, I am trying for a more
automated worksheet. As well the user may inadvertently select the incorrect
title.

 

[Shane Devenshire]

Hi,

You can't prevent every user error. If you automate the title with a
VLOOKUP, then you need to make sure they enter their name in a consistant
form. For example, John instead of J. Smith, would fail, as would Smith or
Smith, John, or J. P. Smith.

All of that said, your VLOOKUP would use a table like this

M N
Tom Jones Mr.
Ali McGraw Ms.
A. Lincoln President
....

If these are in the range M1:N30 and the user types their name into A2 with
their title appearing in B2, the formula in B2 would be:

=VLOOKUP(A2,M$1:N$30,2,)

You might consider giving the users a drop down list for their names, using
Data, Validation, List... That would eliminate the chance for a name entered
in an incorrect form. And although they might pick the wrong title, they are
not likely to pick the wrong name.

 

[AccAdmin]

Shane

Worked in the formula but it returned a #N/A value. here is the way it was
set up, I did it this way first to run a test to see if it would work or not
(basically this is the way the worksheet is set up).

P1 Q1
John Doe Administrator
Jane Doe Sales

I also created a drop down list in B54 for the user to select a User Name, I
then placed the formula in B56 as follows.

=VLOOKUP(B54,$P$1:$Q$2,56,)

This is what returned
VALUE RETURNED IN B56 = #N/A

any help would be appreciated.

PS I do have cells merged in the signature area.