Solved Drop down list when click shows price

February 12, 2013 at 03:11:01
Specs: Windows 7 and xp
Hi can you have a drop down list of items say motors of different type. When you click the type the price comes up in another cell to be used in a quote list. In Excel

See More: Drop down list when click shows price

Report •


#1
February 12, 2013 at 03:26:14
Hi

The short answer is yes, this is very commonly done in excel. There are several ways to achieve this, the most common is where you would have a table listing all the items and their prices elsewhere on the worksheet or even another worksheet. Then, when you make a selection with the drop down you would use a lookup statement to get the price for the item selected.

To give you more detailed help we would need to know the set up of your data.


Report •

#2
February 12, 2013 at 04:12:32
Hi Altek thanks for replying. Didnt expect such a quick reply. I am in the office of a small plumbing business and am new to excel. We send out quotes for different jobs. I have set up a spreadsheet with parts and items and different cost for different jobs. In the spreadsheet I have placed a drop down list of different types of gas heaters and makes. What I need is when I click on the drop down list and choose a gas heater two things happen: One the name of the gas heater appears in the quote list, and the price appears next to it so it can be auto added to the final quote. Thanks for your help. Whers can I find out about Lookup Statement.
Bazza

Report •

#3
February 12, 2013 at 05:56:53
✔ Best Answer
re: "Where can I find out about Lookup Statement."

You can find out a lot about using VLOOKUP in both the Excel Help files right on your machine or via a Google search.

Once you have familiarized yourself with the function, come on back if you need help with your specific task.

Once you get comfortable with VLOOKUP, you might want to go the next step and consider Dependent Drop Down lists.

With Dependent Drop Down lists you could create a list of manufacturers in a Drop Down. Once a manufacturer was chosen, a second Drop Down would automatically populate with the models specific to that manufacturer. Once a model was chosen, the price could be retrieved via VLOOKUP.

With Dependent Drop Downs, keeping the manufacturer/model number lists can be a little easier since you would have smaller groupings instead of one long drop down list with every manufacturer and model in your inventory. When models and/or prices change, you would change the values in the range specific to that manufacturer.

See here for instructions on using Dependent Drop Down Lists:

http://www.contextures.com/xlDataVa...


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 12, 2013 at 20:17:28
Thank guys for your help have managed to do most of the of what I need with data validation, Vlookup and indirect. Just a couple of problems. I worked it all out on my data sheet but when I try to put it in the actual quote sheet I can bringup data Validation but can not go to the data sheet to pick up the tables. I originally protected the sheets and workbook I have deselected that. The other problem is in the top lefthand corner when I click the arrow instead of a couple of lists I have every gas heater is there anyway I can delete them.
Thanks again
Bazza

Report •

#5
February 12, 2013 at 22:57:05
I believe the first problem might be that validation requires that you set up a named table and refer to that in the validation. If you need help setting that up feel free to ask.

I don't understand the second issue. Can you re-explain.


Report •

#6
February 13, 2013 at 13:39:32
I fixed the first problem by by copying the workbook and pasting it back under a different name. The second problem was the top left where the cell address is A1, A2, B1, B2 etc When I click the arrow I have all the Gas Heaters as well as the group names in the dropdown list, is this to be expected? If not how can I delete them from that list? Just one more question can you change the format of the drop down lists in my quote sheet? The letters seem small It would be better if I made them larger. Again thanks alot this is really helping the business run more smoothly and efficently. Bazza

Report •

#7
February 13, 2013 at 15:59:36
Without seeing samples of your data, and the problem you are having, it's hard to offer a solution.

What do you mean by you have "all the gas heaters and the group names" in the drop down?

Whatever is in the range of cells that the drop down refers to will appear in the list. If you don't want something to appear in the list, don't include it in the range that the list is built from.

As far as increasing the font of the drop down, you can't. However, you can "fake it" to a certain extent.

See the workarounds at this site:

http://www.contextures.com/xlDataVa...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 18, 2013 at 03:36:10
Hi Guys just want to thank you both again. The biggest problem that I had, was I did the original spreadsheet on my home PC in Excel 2010, but the one at work had Excel 2007. I didnt realise the differences which made it work on 2010 but not 2007. Once I found out the difference I was more careful in the way I wrote the formula.
Bazza

Report •

#9
February 18, 2013 at 04:01:53
Would it be possible for you to tell us what the issue was?

I am not aware of any difference with VLOOKUP in 2007 vs. 2010.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 18, 2013 at 18:21:41
It wasnt the VLOOKUP that was the problem it was the DATA VALIDATION. When I did the program in 2010 it worked great. But when I tried it in 2007 the drop down arrows didnt appear. Also when I tried to do DATA VALIDATION in 2007, I couldnt click on another sheet at the bottom to find the location of the source. Also if you use F3 it bring the range up once but if you pick the wrong range you need to go out of that sheet and return to use F3 again. I hope I have explained it well. Can you send me in the right direction for this question. On this spreadsheet I have places where we fill in the clients name, address, telephone etc. What I would like to do is when we save the sheet this information also goes to a another sheet which saves and organises that data, so I can use the information to send out invoices. Then the original sheet has the emty spaces ready to be filled in for the next client. Thanks again Bazza

Report •


Ask Question