Hello, I work on a help desk in a call center and we have a nifty
worksheet where we can select the agent who calls us from a drop-down
list. The problem is, we also have to select the supervisor from
another drop-down list. I have been doing research on how to stream
line this process by having the supervisor's name automatically
displayed when we select the the agent's name from the drop-down list.
Unfortunately, the guy who designed this worksheet no longer is with the
company. I have already modified it to give statistics on the reps who
call, but i'm trying to stream line the entering process. I know how
to make drop-down lists and everything, but i'm having trouble with the
returning value part. After some research, it looks like the VLOOKUP
function will be the best bet, but i don't understand all of the
arguments you have to enter. My data looks like the following:

Worksheets: Entered!, Data!

on Data!:
A B
AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3 etc.

on Entered!, I want the following to happen:
A B
(when select) AgentName1 (want it to return) Supervisor1
(when select) AgentName4 (want it to return) Supervisor3 etc.

From what I have found, the formula needs to look something like this:
On Entered!:
A B
=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

I don't understand what the 2 or the false does and that might be the
problem, though i'm sure it also has something to do with my cell
references. Does anyone have any idea on how to get this to work?

My second question is how do you get a default value to be displayed
until you make a selection from the drop down list? The way I have my
new sheet setup, it's blank until you make a selection. Any and all
help will be greatly appreciated!!

Thanks!

Curiously yours,
Andrew


--
Budamon

Hi!

About Vlookup.........

=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

The first argument, A1, is the lookup_value. This is the value you want to
lookup and the place you want to look for that value is the table_array
which is the second argument, Data!A1:B1. The lookup_value is searched for
in the leftmost column of the table_array. If the lookup_value is found you
have to tell the function what column of the table_array contains the value
to return that corresponds to the lookup_value. This is the third argument,
col_index_num. In your formula the col_index_num is 2. The col_index_num is
relative to the table_array. If the table_array range was Z1:AA10 the
col_index_num would still be 2 because it's the second column in relation to
the table_array even though it's physical location in the worksheet is
column 27.

The fourth argument is range_lookup. This argument is optional and if
ommited, defaults to TRUE. This argument is used to tell the function if the
table_array is sorted or not sorted for the purpose of finding either an
exact match or the closest match. TRUE if it's sorted, FALSE if it's not
sorted. You would use an argument of FALSE if you want to look for an exact
match. You would use TRUE if you want the closest match that is less than
the lookup_value if there is no exact match. Using the TRUE argument
requires that the table_array be sorted in ascending order to work properly.
The TRUE argument is used mostly for numeric ranges but also works for text.

OK, let's try to figure out why your formula is returning an error.

=VLOOKUP(A1, Data!A1:B1, 2, false)

As written, the table_array consists of only 2 cells and one of those is
supposed to contain the lookup_value. You have to increase the size of your
table_array to match the range size of your table:

AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3

Based on that table it would be:

=VLOOKUP(A1, Data!A1:B4, 2, false)

I'm assuming your drop down list is in cell A1 which is the lookup_value. If
there is no selection made from the drop down and cell A1 is empty then the
formula will return #N/A because there is no matching empty cell in the
table_array. If there is a selection made from the drop down but that value
does not exist in the table_array (using the FALSE 4th argument) then the
formula will return an #N/A error.

You can build into the formula tests that will check for both of these
conditions and define some value to return instead of the #N/A error. To do
this we need to use an IF function.

The most popular method to do this is:

=IF(ISNA(VLOOKUP(A1,Data!A1:B4, 2, false)),"",VLOOKUP(A1, Data!A1:B4, 2,
false))

This formula does a first lookup and if the result is #N/A, returns a blank
cell. If the first lookup does not result in #N/A then it does a second
lookup and returns the the appropriate value.

Biff

"Budamon" wrote in message
...

Hello, I work on a help desk in a call center and we have a nifty
worksheet where we can select the agent who calls us from a drop-down
list. The problem is, we also have to select the supervisor from
another drop-down list. I have been doing research on how to stream
line this process by having the supervisor's name automatically
displayed when we select the the agent's name from the drop-down list.
Unfortunately, the guy who designed this worksheet no longer is with the
company. I have already modified it to give statistics on the reps who
call, but i'm trying to stream line the entering process. I know how
to make drop-down lists and everything, but i'm having trouble with the
returning value part. After some research, it looks like the VLOOKUP
function will be the best bet, but i don't understand all of the
arguments you have to enter. My data looks like the following:

Worksheets: Entered!, Data!

on Data!:
A B
AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3 etc.

on Entered!, I want the following to happen:
A B
(when select) AgentName1 (want it to return) Supervisor1
(when select) AgentName4 (want it to return) Supervisor3 etc.

From what I have found, the formula needs to look something like this:
On Entered!:
A B
=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

I don't understand what the 2 or the false does and that might be the
problem, though i'm sure it also has something to do with my cell
references. Does anyone have any idea on how to get this to work?

My second question is how do you get a default value to be displayed
until you make a selection from the drop down list? The way I have my
new sheet setup, it's blank until you make a selection. Any and all
help will be greatly appreciated!!

Thanks!

Curiously yours,
Andrew


--
Budamon

Biff, thank you SO much! ever consider teaching as a career? :) I
can't wait to get to work (did i just say that?!?) and try it out.
Thanks again and keep up the great work!

Regards,
Andrew
Biff Wrote:
Hi!

About Vlookup.........

=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

The first argument, A1, is the lookup_value. This is the value you want
to
lookup and the place you want to look for that value is the
table_array
which is the second argument, Data!A1:B1. The lookup_value is searched
for
in the leftmost column of the table_array. If the lookup_value is found
you
have to tell the function what column of the table_array contains the
value
to return that corresponds to the lookup_value. This is the third
argument,
col_index_num. In your formula the col_index_num is 2. The
col_index_num is
relative to the table_array. If the table_array range was Z1:AA10 the
col_index_num would still be 2 because it's the second column in
relation to
the table_array even though it's physical location in the worksheet is
column 27.

The fourth argument is range_lookup. This argument is optional and if
ommited, defaults to TRUE. This argument is used to tell the function
if the
table_array is sorted or not sorted for the purpose of finding either
an
exact match or the closest match. TRUE if it's sorted, FALSE if it's
not
sorted. You would use an argument of FALSE if you want to look for an
exact
match. You would use TRUE if you want the closest match that is less
than
the lookup_value if there is no exact match. Using the TRUE argument
requires that the table_array be sorted in ascending order to work
properly.
The TRUE argument is used mostly for numeric ranges but also works for
text.

OK, let's try to figure out why your formula is returning an error.

=VLOOKUP(A1, Data!A1:B1, 2, false)

As written, the table_array consists of only 2 cells and one of those
is
supposed to contain the lookup_value. You have to increase the size of
your
table_array to match the range size of your table:

AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3

Based on that table it would be:

=VLOOKUP(A1, Data!A1:B4, 2, false)

I'm assuming your drop down list is in cell A1 which is the
lookup_value. If
there is no selection made from the drop down and cell A1 is empty then
the
formula will return #N/A because there is no matching empty cell in
the
table_array. If there is a selection made from the drop down but that
value
does not exist in the table_array (using the FALSE 4th argument) then
the
formula will return an #N/A error.

You can build into the formula tests that will check for both of these
conditions and define some value to return instead of the #N/A error.
To do
this we need to use an IF function.

The most popular method to do this is:

=IF(ISNA(VLOOKUP(A1,Data!A1:B4, 2, false)),"",VLOOKUP(A1, Data!A1:B4,
2,
false))

This formula does a first lookup and if the result is #N/A, returns a
blank
cell. If the first lookup does not result in #N/A then it does a
second
lookup and returns the the appropriate value.

Biff

"Budamon" wrote in message
...

Hello, I work on a help desk in a call center and we have a nifty
worksheet where we can select the agent who calls us from a drop-down
list. The problem is, we also have to select the supervisor from
another drop-down list. I have been doing research on how to stream
line this process by having the supervisor's name automatically
displayed when we select the the agent's name from the drop-down
list.
Unfortunately, the guy who designed this worksheet no longer is with
the
company. I have already modified it to give statistics on the reps
who
call, but i'm trying to stream line the entering process. I know how
to make drop-down lists and everything, but i'm having trouble with
the
returning value part. After some research, it looks like the VLOOKUP
function will be the best bet, but i don't understand all of the
arguments you have to enter. My data looks like the following:

Worksheets: Entered!, Data!

on Data!:
A B
AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3 etc.

on Entered!, I want the following to happen:
A B
(when select) AgentName1 (want it to return) Supervisor1
(when select) AgentName4 (want it to return) Supervisor3 etc.

From what I have found, the formula needs to look something like
this:
On Entered!:
A B
=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

I don't understand what the 2 or the false does and that might be the
problem, though i'm sure it also has something to do with my cell
references. Does anyone have any idea on how to get this to work?

My second question is how do you get a default value to be displayed
until you make a selection from the drop down list? The way I have
my
new sheet setup, it's blank until you make a selection. Any and all
help will be greatly appreciated!!

Thanks!

Curiously yours,
Andrew


--
Budamon


--
Budamon