vlookup on cells with Active-x or form controls

[bgonen]

Is there a way to use lookup formulas on cells that are picked from a Combo box or any other control buttons ?

 

[RoryA]

I'm not really sure what you mean. If the controls return a value to a cell then yes you can use that value in other formulas.

 

[bgonen]

yes the control return a value but when I try to lookup this specific cell the formula won't work

 

[RoryA]

In what way does it not work? (at a guess I'd say you are looking up a number value, but the value returned by the control may be text.)

 

[bgonen]

yes, I'm trying to pull a number value (for example:2789001)
but according to you; it is pull as TEXT (so that's why the Lookup formula is giving me #N/A,,,)

Is there a way to force the cell to pull value rather than text?

 

[RoryA]

No - you need to adjust your formula so that instead of say:
=lookup(A1,somerange)
it is:
=lookup(--A1,somerange)

 

[bgonen]

I tried the trick but it didn't work (and I know my formula is good because without the drop down button , the values are pulled correctly)

 

[RoryA]

What is the actual formula and can you post a version that works using a literal lookup value rather than a cell reference?

 

[bgonen]

sorry, I don't know how to show a literal formula
but D2 is the value I'm trying to pull from the control button
C3:I7 is a range of rates
I37 is volume

=VLOOKUP(--$D$2,$C$3:$I$7,7,FALSE)*I37

Please don't give up on me,,,

 

[RoryA]

What is in D2 and which if these two formulas works:
=VLOOKUP(2789001,$C$3:$I$7,7,FALSE)*I37
or:
=VLOOKUP("2789001",$C$3:$I$7,7,FALSE)*I37
(using the sample number you provided earlier)