Control Box Data Question
 

I am using a control box list to drop down the various part numbers that we
use. I then use that number as part of a VLOOKUP function to return
corresponding data. This problem is that VLOOKUP sees the number in the
linked cell with " " around it. Why? and how do I get rid of it? The
quotes do not effect looking up in text fields and I can get rid of them by
adding a second step where the linked cell is multiplied by 1 and that cell
is then the reference cell for VLOOKUP. I can also get around this by
adding " " to my lookup numbers so they match , but either case leaves my
curiosity unfulfilled as to a more elegant solution.

The start to this was not knowing how to change the font/color/etc. in the
Data-Validation-List box. So any help answering that question would also be
appreciated. Thanks in advance, and since I've been learning excel, I've
gotten several tips from people answering others, thank you as well.

Chris Cameron

=VLOOKUP(SUBSTITUTE(A1,"""",""),C1:D120,2,0)

Set ranges to taste

Lance

"camerons" wrote:

I am using a control box list to drop down the various part numbers that we
use. I then use that number as part of a VLOOKUP function to return
corresponding data. This problem is that VLOOKUP sees the number in the
linked cell with " " around it. Why? and how do I get rid of it? The
quotes do not effect looking up in text fields and I can get rid of them by
adding a second step where the linked cell is multiplied by 1 and that cell
is then the reference cell for VLOOKUP. I can also get around this by
adding " " to my lookup numbers so they match , but either case leaves my
curiosity unfulfilled as to a more elegant solution.

The start to this was not knowing how to change the font/color/etc. in the
Data-Validation-List box. So any help answering that question would also be
appreciated. Thanks in advance, and since I've been learning excel, I've
gotten several tips from people answering others, thank you as well.

Chris Cameron

=VLOOKUP(Value(A1),C1:D120,2,0)
=VLOOKUP(A1*1,C1:D120,2,0)

Thanks Lance, these two also took care of it