Formulas employing cells linked to a combo box drop list

[Guest]

I am having problems employing a formula based on a cell that's linked to a combo box.

I've set up the combo box to display a drop list of numbers in a column in the same worksheet. I've linked that combo box to an adjacent cell, so that when a number is selected from the drop down box, it is displayed in the linked cell

The problem arises when I try to create a formula (for what it's worth, it's a match function nested within a VLookup function) that uses the linked cell as an input. For some reason, the linked cell does not seem to be recognized by the formula, which results in a #NA error.

I suspect that the linked cell is not formatted correctly to display the combo box selection as a number, but Excel does not seem to allow me to change the format of the linked cell. Is there something within the "properties" menu for the combo box that will allow me to designate the linked cell as a number, so that that cell can then be used within formulas in other cells in the worksheet

Thanks in advance

D&C

 

[Frank Kabel]

Hi
if your linked cell is B1 try
=VLOOKUP(--B1,lookup_rnage,col_index,0)

--
Regards
Frank Kabel
Frankfurt, Germany

I am having problems employing a formula based on a cell that's linked to a combo box.

I've set up the combo box to display a drop list of numbers in a

column in the same worksheet. I've linked that combo box to an adjacent
cell, so that when a number is selected from the drop down box, it is
displayed in the linked cell.

The problem arises when I try to create a formula (for what it's

worth, it's a match function nested within a VLookup function) that
uses the linked cell as an input. For some reason, the linked cell does
not seem to be recognized by the formula, which results in a #NA error.

I suspect that the linked cell is not formatted correctly to display

the combo box selection as a number, but Excel does not seem to allow
me to change the format of the linked cell. Is there something within
the "properties" menu for the combo box that will allow me to designate
the linked cell as a number, so that that cell can then be used within
formulas in other cells in the worksheet?

 

[Frank Kabel]

Hi
try
=VLOOKUP(--D3,TABLE1,1,0)

or please post your current used formula. just add the double minus in
front of your first parameter!


--
Regards
Frank Kabel
Frankfurt, Germany

Thanks so much for your help, Frank. It's much appreciated.

Unfortunately, I just need a bit of further clarification.

When I copy the exact formula you wrote, using the proper reference

for the linked cell (in my case, D3), I get a #NAME error.

I tried a variation using VLOOKUP whereby I created a table out of

the data that the combo box references to see if that helped
=VLOOKUP(D3,TABLE1,1) and get a #NA error.

I even tried to use =INDEX(AA1:AA18,D3) -- the column that the combo

box references is AA1-AA18 -- I get a #REF error.

 

[Frank Kabel]

Hi
no, the col_index defines the column from which the value should be
returned. VLOOKUP searches always in the first column of your range.
See:
http://www.mvps.org/dmcritchie/excel/vlookup.htm

--
Regards
Frank Kabel
Frankfurt, Germany

One last quick question: in the formula =VLOOKUP(--D3,TABLE1,1,0), I

imagine that the "1" designates the column within the table where the
VLOOKUP formula is looking to match the value that appears in the
linked cell D3.

When I create a second combo box (linked to cell D5) that pulls data

from the next column over in TABLE1, and try the formula
=VLOOKUP(--D5,TABLE1,2,0), I get a #NA error.

Strangely, the first row of every column in my TABLE1 is a 0 -- I've

set it up this way so that the combo boxes can have a "null"
selection -- and when I select 0 in the combo box, the VLOOKUP formula
correctly displays a 0. When I select any other option in the drop down
menu of the combo box, however, the VLOOKUP formula returns the #NA
error.