Home |
Search |
Today's Posts |
#1
|
|||
|
|||
simple but I am not bright!
I have been at this since 7 am this morning and still have not gotten it. Hope you guys can help. Task 1: Cursor on A1 would show 20 choices (kp, ta, sw....) on pop-up box Task 2: Click one of the choice yield a an associated 6-digit number (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 on G1. Again, thanks so so so much for your help! -- bellaroxio ------------------------------------------------------------------------ bellaroxio's Profile: http://www.excelforum.com/member.php...o&userid=13142 View this thread: http://www.excelforum.com/showthread...hreadid=391593 |
#2
|
|||
|
|||
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: DataValidation --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 -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=391593 |
#3
|
|||
|
|||
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 -- Greetings from New Zealand Bill K "bellaroxio" wrote in message ... I have been at this since 7 am this morning and still have not gotten it. Hope you guys can help. Task 1: Cursor on A1 would show 20 choices (kp, ta, sw....) on pop-up box Task 2: Click one of the choice yield a an associated 6-digit number (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 on G1. Again, thanks so so so much for your help! -- bellaroxio ------------------------------------------------------------------------ bellaroxio's Profile: http://www.excelforum.com/member.php...o&userid=13142 View this thread: http://www.excelforum.com/showthread...hreadid=391593 |
#4
|
|||
|
|||
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 "bellaroxio" wrote: I have been at this since 7 am this morning and still have not gotten it. Hope you guys can help. Task 1: Cursor on A1 would show 20 choices (kp, ta, sw....) on pop-up box Task 2: Click one of the choice yield a an associated 6-digit number (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 on G1. Again, thanks so so so much for your help! -- bellaroxio ------------------------------------------------------------------------ bellaroxio's Profile: http://www.excelforum.com/member.php...o&userid=13142 View this thread: http://www.excelforum.com/showthread...hreadid=391593 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the color of Filter Arrows in Excel to BRIGHT RED | Excel Discussion (Misc queries) | |||
simple if then function | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Help with what should be a simple formula | Excel Worksheet Functions | |||
Simple But Stumped | Excel Discussion (Misc queries) |