Combo-box useage

  • Thread starter Thread starter MatthewOKC
  • Start date Start date
M

MatthewOKC

I am trying to create a form where I can use the combo box to choose a
item and when I choose this item, it would automatically put a value in
the next column.

Such as I choose a parts name from the combo-box and in the next column
it automatically puts the p/n or price for that part.

That is what I am looking to do

Thanks
MatthewOKC
 
Hi Matthew

by "form" i'm assuming you mean a formatted worksheet rather than a
userform in vba? if so, here's an answer i posted to a similar question a
couple of days ago
***
use a combination of data / validation to create the drop down list and
VLOOKUP function to populate the related information
assuming that in Sheet2 you have the following
A B
1 Item Value
2 Item1 10.00
3. Item2 15.00
4. Item3 20.00

now select from A1 to the end of your list (A4, in the above example) and
choose from the menu,
Insert / Name / Create - ensure Top Row is checked and click okay - you've
created a named range called "Item" (or whatever the title in A1 is)

now select from A1 to the end of the list for all columns (B4 in the above
example) and click inside the name box (left of formula bar) and type
MyTable and press enter - we've created a second named range.

Now click in the cell in Sheet1 where you want your drop down list to appear
and choose Data / Validation from the menu - in the settings tab, choose
List from the Allow drop down box and then click inside the white box under
this and press the F3 key - this will bring up a list of your range names,
choose "Item" (or whatever your first range was called) and click Ok. You
will now have a drop down list in this cell.

Now click in the cell where you want the related information to appear and
type
=VLOOKUP(A1,MyTable,2,false)
where A1 is the cell reference with your drop down list in it - you can
use
the F3 key for the MyTable bit too.

now choose an item & see the related information appear ... delete the item
and you'll get
a #NA error - this can be supressed by embedding your VLOOKUP in an IF
statement e.g.

=IF(A1="","",VLOOKUP(A1,MyTable,2,false))
where A1 is the cell reference with your drop down list in it

hope this helps, let us know how you go

Cheers
JulieD
 
I am having trouble with the "=VLOOKUP(A1,MyTable,2,false) statement,
don't know wht the "MyTable" should be, when I try different things i
gives me a error type message, also the "F3" does not do anything, i
there something I need to turn on to make it work?
Thanks
 
MyTable is supposed to be the table you are looking up,
you can select the table, do insert>name>define and give it a name
or you can replace the name with a range reference like

VLOOKUP(A1,Sheet2!A2:B200,2,FALSE)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
or if you are going to use the VLOOKUP for more than one line ..

VLOOKUP(A1,Sheet2!$A$2:$B$200,2,FALSE)

you might like to check out Debra Dalgleish's site for details on naming
ranges .
http://www.contextures.com/tiptech.html

Cheers
JulieD


Peo Sjoblom said:
MyTable is supposed to be the table you are looking up,
you can select the table, do insert>name>define and give it a name
or you can replace the name with a range reference like

VLOOKUP(A1,Sheet2!A2:B200,2,FALSE)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



message news:[email protected]...
Click to expand...
 
Back
Top