Excel Combobox

[PeteR]

1. Using a combobox on a worksheet to list available
values listed in another worksheet (the listed values are
contained in a column of formatted as text).
2.Return the value selected to a cell that is formatted as
text.
3.Use this cell value to do a vlookup to find other values
on the same row as the listed value.

1 and 2 work fine, but using the vlookup does not work as
expected. When the cell value is a text string containing
only numbers I have to use the "value" function for the
vlookup to be succesful. When the the cell value is made
up of characters (and numbers) the "value" function
obviously fails. By not using the "value" function it
works fine on character values but fails on numbers. I
want it to work on both.

Example of the vlookup:

=VLOOKUP(VALUE(B11),NewBearing.xls!BallList,3,FALSE)

 

[Frank Kabel]

Hi
some workarounds:
1. Also format your referenced list (ballliost) as text.
This way you should avoid these problems.

2. You may use something like the following:
=IF(ISERROR(VLOOKUP(--B11,NewBearing.xls!
BallList,3,FALSE),VLOOKUP(B11,NewBearing.xls!
BallList,3,FALSE),VLOOKUP(--B11,NewBearing.xls!
BallList,3,FALSE))

Note: I replaced your VALUE function call with the unary
operator '--'
The result is the same in both cases. Just a matter of
taste

 

[hglamy]

Well done, Frank,
excellent answer !

Thank you so much,

hglamy

 

[PeteR]

Thanks Frank.

Just tried and it works ok.

In this instance using the unary operator makes it more
readable.