Displaying blank field instead of #N/A
 

I have created a form to be completed requesting a server be moved from one
location to another. The mover needs to see the server name and the server
type. Starting in cell B77 (and going down the column) I have listed all the
servers. Starting in cell C77 I have listed the type of server named in cell
B77.

In my form, I used the Data Validation function, I have created a drop down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and if it
sees the name of a server from the list in cells B77:C529 it them displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!

Hi!

Try this:

=IF(B21="","",VLOOKUP(B21,B77:C529,2))

Biff

"Joe Hays" <Joe wrote in message
...
I have created a form to be completed requesting a server be moved from one
location to another. The mover needs to see the server name and the
server
type. Starting in cell B77 (and going down the column) I have listed all
the
servers. Starting in cell C77 I have listed the type of server named in
cell
B77.

In my form, I used the Data Validation function, I have created a drop
down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and if
it
sees the name of a server from the list in cells B77:C529 it them displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!

Thanks for the help Biff. I tried your suggestion, but it did not work, #N/A
is still displayed instead of a blank cell.

"Biff" wrote:

Hi!

Try this:

=IF(B21="","",VLOOKUP(B21,B77:C529,2))

Biff

"Joe Hays" <Joe wrote in message
...
I have created a form to be completed requesting a server be moved from one
location to another. The mover needs to see the server name and the
server
type. Starting in cell B77 (and going down the column) I have listed all
the
servers. Starting in cell C77 I have listed the type of server named in
cell
B77.

In my form, I used the Data Validation function, I have created a drop
down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and if
it
sees the name of a server from the list in cells B77:C529 it them displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!

The only way that would be possible would be if the error comes from not
finding the value from B21
in the lookup table also note that you are not looking for an exact match,
try

=IF(ISNUMBER(MATCH(B21,B77:B529,0)),VLOOKUP(B21,B7 7:C529,2,0),"")

--
Regards,

Peo Sjoblom


"Joe Hays" wrote in message
...
Thanks for the help Biff. I tried your suggestion, but it did not work,
#N/A
is still displayed instead of a blank cell.

"Biff" wrote:

Hi!

Try this:

=IF(B21="","",VLOOKUP(B21,B77:C529,2))

Biff

"Joe Hays" <Joe wrote in message
...
I have created a form to be completed requesting a server be moved from
one
location to another. The mover needs to see the server name and the
server
type. Starting in cell B77 (and going down the column) I have listed
all
the
servers. Starting in cell C77 I have listed the type of server named
in
cell
B77.

In my form, I used the Data Validation function, I have created a drop
down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and
if
it
sees the name of a server from the list in cells B77:C529 it them
displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!

Thanks Peo!! Your suggestion worked perfectly!

"Peo Sjoblom" wrote:

The only way that would be possible would be if the error comes from not
finding the value from B21
in the lookup table also note that you are not looking for an exact match,
try

=IF(ISNUMBER(MATCH(B21,B77:B529,0)),VLOOKUP(B21,B7 7:C529,2,0),"")

--
Regards,

Peo Sjoblom


"Joe Hays" wrote in message
...
Thanks for the help Biff. I tried your suggestion, but it did not work,
#N/A
is still displayed instead of a blank cell.

"Biff" wrote:

Hi!

Try this:

=IF(B21="","",VLOOKUP(B21,B77:C529,2))

Biff

"Joe Hays" <Joe wrote in message
...
I have created a form to be completed requesting a server be moved from
one
location to another. The mover needs to see the server name and the
server
type. Starting in cell B77 (and going down the column) I have listed
all
the
servers. Starting in cell C77 I have listed the type of server named
in
cell
B77.

In my form, I used the Data Validation function, I have created a drop
down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and
if
it
sees the name of a server from the list in cells B77:C529 it them
displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!