General discussion

Locked

Excel

By leonaedanukam ·
I have a project in which I have to build a drop down menu in excel and a user chooses one or moer items from the menu and the excel calculates the total cost. Am not good in excel, I want an idea on how to do this.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel

by DKlippert In reply to Excel

Here's a very nice tutorial:

www.contextures.com/xlDataVal01.html

Collapse -

Excel

by leonaedanukam In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by kees.valkenswaard In reply to Excel

If you are not that good in Excel I would use validation [menu choice DATA] in combination with VLOOKUP.
In this case I have to assume a list with the names of the items e.g. in column A, with the costs next to it in column B. [more data could be in other columns] Give a range name to items in A [which would be on sheet2], e.g. ItList

Next I have to assume that you will want to choose a maximum of say 10 items. If you need more, you can just propagate the formulas with the fill-handle.
Allyou have to do in the validation screen is to specify that you wan to limit the items to a list. For the list just put =ItList [do not forget the =, it is a formula and not text]
You can specify on another tab if a wrong entry should result in an error, a warning etc. In your case I would choose error.
Suppose this is all in Column C on sheet1 [just propagate the validation downwards. Do this before entering a value]
Maybe you want to specify the amount of items in column B.
In column D youcould retrieve the unit price and in column E the total price for this item.
Give also a range name to both items and unit price on Sheet2, say TotList
Suppose you have entered a value in cell C2 [with the validation]
In D2 your formula could be:
=VLOOKUP(B2;TotList;2;false)
The result of this would be the unit price.
To improve the formula, use:
=IF(ISERROR(VLOOKUP(B2;TotList;2;false));""; VLOOKUP(B2;TotList;2;false))
This would result in no text or value as long no value is in the C cell [or a wrong value not in the range]
Users will choose an item from the list as many times as they need [each time in the next row]
It is possible to make it all much nicer with forms, VBA and more gimmicks as shortening the list, once an item has been chosen]. I would start with this.
NOTE You may live in a country where not ; [semi colon] is the list separator but , [comma]
Amend the formulas accordingly.
Good luck

Collapse -

Excel

by leonaedanukam In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by leonaedanukam In reply to Excel

This question was auto closed due to inactivity

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums