Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
=VLOOKUP(Value(A1),C1:D120,2,0)
=VLOOKUP(A1*1,C1:D120,2,0) Thanks Lance, these two also took care of it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Control pasted/imported data to use only ODD or EVEN rows | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Another question on how to find duplicate data | Excel Worksheet Functions | |||
Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions |