simple but I am not bright!

B

bellaroxio

:confused: I have been at this since 7 am this morning and still hav
not gotten it. Hope you guys can help.

Task 1: Cursor on A1 would show 20 choices (kp, ta, sw....) on pop-u
box
Task 2: Click one of the choice yield a an associated 6-digit numbe
(144241, xxxxxx...) on B2
Task 3: H1 would show associated Unit price for choice on A1.
Task 4: I1 would show total $ depending what quantity is entered o
G1.

Again, thanks so so so much for your help!

[email protected]
 
R

Ron Coderre

Step 1: Make a table on another sheet with these example headings:
Choice, ItemID, UnitPrice

Step 2: Fill in the items in the table

Step 3: Select the table cells and name the range LU_Items

Step 4: Select only the Choice items (not including the heading) and
name that range LU_Choice

Now go back to your initial sheet:
Cell A1: Data>Validation>
-->Allow: List
-->Source: =LU_Choice

Cell B2(ItemID): =VLOOKUP(A1,LU_Items,2,0)
Cell G1(Qty Input cell): some number
Cell H1(Price): =VLOOKUP(A1,LU_Items,3,0)
Cell I1: =G1*H1

Does that help?

Ron
 
B

Bill Kuunders

start with a small table to show all the products with their abriviations, 6
digit numbers and the price
for this example it is lokated in K1 to M20
select A1
select data<>validation <> allow..... list
select the list to be the first column of your table K1:K20
in cell B2 enter =VLOOKUP(A1,K1:M20,2,FALSE) to get the number
in cell H2 enter =VLOOKUP(A1,K1:M20,3,FALSE) to get the price
in cell I2 enter =G2*H2

I assumed you will have some column headings therefor B2 H2 I2 and G2
rather than B2 H1 I1 G1
 
J

JMB

One approach:

First, I would create a table on a separate sheet with the 20 item names in
the first column, 6 digit number in the second column, and unit price in the
third column.

Then, I would insert a combobox in cell A1. Use the combobox from the
control toolbox toolbar, not the forms toolbar (click View/Toolbars/Control
Toolbox to bring up the toolbar).

After you've drawn the combobox on the screen, click the properties button
on the control toolbox toolbar (should be second from left). In the section
called ListFillRange enter the reference to the 20 items from your table you
want to appear as choices in the combobox.

For example, if the table is on Sheet2 and your 20 item names are in cells
A1:A20

Sheet2!$A$1:$A$20 would go in the ListFillRange

Now, double click on the combobox, you should now be in the visual basic
editor. Enter this code (the private sub and end sub lines should already be
on your screen - if this is not the first combobox you've created, you will
need to change the reference from Combobox1 to suit the name of your combobox)


Private Sub ComboBox1_Change()
ThisWorkbook.Names.Add Name:="Selection", RefersTo:=ComboBox1.Value
End Sub


In B2, you can use the VLookup function (you will need to change the second
argument to refer to wherever you put your table - or name the table and just
use the name as the second argument).

=VLOOKUP(Selection,Sheet2!$A$1:$C$20,2,FALSE)

Cell H1 would be very similar, but change it to return the third column from
your table.

=VLOOKUP(Selection,Sheet2!$A$1:$C$20,3,FALSE)

Cell I1 would be

=H1*G1
 
Top