I have one problem based on Excel spreadsheet drop down menu?

Question

I have one problem based on Excel spreadsheet drop down menu.I want two columns-one with products and other is its corresponding rates.I want to know how to get the respective price once a particular product is selected from the down menu.Also is it possible to calculate the amount by multiplying quantity with rate.Reply back with an example to understand it better and quickly.

Answer 1

Here's the demo for the products consumed value worksheet http://www.freewebs.com/swhtam/YA/productTotal.xls

1. use a sheet for the table which list the product and its price, then

2. select cells that contain product names, go to menu, insert, name, define, put the word "products" on the 1st box, click ok

3. select cells that contain prices, go to menu, insert, name, define, put the word "Prices" on the 1st box, click ok

4. on another sheet make 4 columns, 1st is for the drop down product list, 2nd is for value that will show up when product is seleted, 3rd is the qty. expecting entry from user, and the last column is total which will show the total when prev. 3 columns are filled.

5. 1st column, select a cell, go to menu, data, validation, settings tab, 1st box choose list, 2nd box put ="products", click ok, copy the cell down to expected No. of rows (in the demo is down to A21)

6. 2nd column put the following formula, say in B2 put

=IF(ISNA( VLOOKUP(A2, Sheet2!A2:B27,2, FALSE))=FALSE, VLOOKUP(A2, Sheet2!A2:B27, 2,FALSE), "")

The formula is a combination of IF and VLOOKUP function, basically, the vlookup fetch the product name from A2 to lookup the product/price table on sheet2 and return the price on B2, if the product name is present in A1 of sheet1, no error return from vlookup part of the formula, then lookup the price from column 2 of the product/price table, otherwise return blank.

7. 4th column for total, say in D2 put

=IF(AND( B2<>0,C2<>0), B2*C2,"")

The formula will first check if B2 the value is there and C2 the qty is there, then it will put the 2 together to come up with the total.

Please contact for more details

Answer 2

If you are doing this using a drop down menu in column a then you would either need to use nested "if" statements in column b to automatically display the price or a database. I don't know how to do either with a drop down box but I'm sure someone does.



After that use the "Sum" feature to multiply the quantity by the cost.

Answer 3

Begin new workbook. On sheet 2 enter your values in contiguous cells, in one column. Spotlight all the cells you may have simply identify define Menu/options or the 'identify box' to the left of you perform bar and form in a reputation for record of values you will have just typed in. E.G. "Mylist" Now go to Sheet 1. In the event you cannot see the visible general Menu, proper click on the software bar or use the Insert, Toolbars and in every case decide upon visual normal. Now on this tool bar click on Design mode, and Toolbox. In finding the Combo field tool, in the software field and click on on it. 'Draw' the combo field onto the sheet the place you want it. Now, again in the toolbox, with the Combo box still chosen, to find and click on the homes box. In the residences window, find the ListFillRange property (this seems in the left hand column) in the corresponding correct-hand column, type in the name of the name of your list - "Mylist" and click enter. Now unclick Design mode, and Toolbox and also close the property box by clicking on the 'x' - close window button. Now click on on the visible general Editor buton on the visual normal oolbar. In the challenge Window, double click on Sheet 1 within the left hand dropdown of the window just opened, choose Combobox1. Whole the code so it appears like the following: confidential Sub ComboBox1_Change() range("A1").Cells.Price = ComboBox1.Textual content finish Sub this will put the chosen worth into mobilephone A1. Excel is very powerful, and there are a lot of options, residences and you have got to be careful when doing the things i've described. I am making use of Excel 2003, and i do know the 'tools' and other objects give a boost to fairly as time goes by using and new types are released. To try this effectively, i might get a booklet on VBA with a focus on VBA for Excel. Anyway, hope this helps.

Answer 4

commence new workbook. On sheet 2 enter your values in contiguous cells, in a unmarried column. spotlight each and each of the cells you've purely entered values into. Use the Insert | call | outline Menu/concepts or the 'call field' to the left of you function bar and type in a recognition for record of values you've purely typed in. E.g. "Mylist" Now flow to Sheet a million. in case you won't be able to see the seen straightforward Menu, properly click on the device bar or use the Insert, Toolbars and in each and each case choose seen straightforward. Now in this device bar click layout mode, and Toolbox. discover the blend field device, interior the device field and click on it. 'Draw' the blend field onto the sheet the position you want it. Now, lower back interior the toolbox, with the blend field nevertheless chosen, discover and click the houses field. interior the houses window, discover the ListFillRange sources (this seems interior the left hand column) interior the corresponding properly-hand column, type interior the call of the call of your record - "Mylist" and click enter. Now unclick layout mode, and Toolbox and also close the sources field through clicking on the 'x' - close window button. Now click on the seen straightforward Editor buton on the seen straightforward oolbar. interior the challenge Window, double click on Sheet a million interior the left hand dropdown of the window purely opened, choose Combobox1. complete the code so it sounds like properly right here: inner most Sub ComboBox1_Change() variety("A1").Cells.fee = ComboBox1.textual content end Sub this can positioned the selected fee into cellular A1. Excel is extremely useful, and there are diverse concepts, homes and also you ought to be careful at the same time as doing the failings I surely have defined. i'm utilizing Excel 2003, and that i know the 'techniques' and different products enhance fairly as time is going through and new variations are released. to attempt this magnificent, i ought to get a e book on VBA with a spotlight on VBA for Excel. besides, wish this helps.

Answer 5

right here we go so sit down and listen! lol

forst you have your products in column A (GAS) and your rate or price or whatever in column b (£35.99pm). now in column C title Qty enter the quatity you have in C1, to work out the Qty X Rate type =sum(b1*c1) ie £39.99 times(*) 4 = £159.96. make sure column b is formatted for currency and poof you got your final figure.