Vlookup and combo box

[Aardvark]

Dear all,

Playing with excel I set up an invoice for a mythical mechanic . The
invoice has 3 columns, part no, part description and cost and the idea was
that the part number could be selected from a combo box and v lookup would
then fill in the description and the price.

The vlookup table has the same three columns. The problem I am having is
that although I can create a combo box to display the part numbers the value
the box returns to the cell is the row number (rather like the match
function) Not the serial number, and so the v lookup fails. eg if I have in
row five part number 00354 fan belt $50 when I select the value 00354 from
the combo box the linked cell returns the value 5. I suppose I could change
the vlookup so that it looks for the row rather than the serial number, but
it seems so strange. Does anyone know if it is possible to get the combo box
to return the selcted value to the linked cell rather than the row number?

Many thanks,

Danny

 

[Dave Peterson]

You may want to use Data|Validation instead of a dropdown. (It sounds like your
combobox is a dropdown from the Forms toolbar.)

But if you want to continue using the dropdown, you could have another cell
return the actual value.

If A1 were the linked cell and a1:a99 contained the input range, you could use
this formula to get the serial number:

=if(a1="","",index(sheet2!a1:a99,A1))

or you could even build it into your =vlookup() formula.

=if(a1="","",vlookup(index(sheet2!$a$1:$a$99,a1),sheet2!$a$1:$d$99,3,false))

to return the value in column C.

If you decide that you want to try Data|Validation and your list is on another
worksheet, you can define a range name and it'll work ok.

See Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html
for tons of info

 

[Aardvark]

Thanks Dave,

The Index command looks interesting so I will have a look at that. Data
validation worked beautifully...provided the data list was on the same
worksheet as the invoice. I tried to get round this by copying the workbook
under another name and creating a workspace so that both the necessary
sheets were "open" (except of course they are not both active) and
referencing the cells in the other workbook for my validation list but alas
this did not work.

I still like data validation as a solution (it seems easier than the others)
and certainly as part of my quest to update my Excel skills it is great.

If you can think of a way to reference the data validation to a list on
sheet 2 in the same work book that would be great.

Thanks again!

Danny

 

[Dave Peterson]

If you give that list a name, you can use that name in the Source box.

Say you name the list: MyList
then put =mylist
in that box

 

[Aardvark]

Mr Peterson,

You are a gem!

Thank you

Danny

If you give that list a name, you can use that name in the Source box.

Say you name the list: MyList
then put =mylist
in that box