Total from drop down list

Topic

New Reply

I have a list of items on a spreadsheet that has several dozen items on a drop down list, then one next to it with quantity.
What I’d like to do is once that pull down is chosen, to be able to total the cost of that item times the quantity in the next drop down into another cell.
Is that possible? if so, it will be extremely helpful.

Thanks!

Reply | Quote

Replies

Will the attached do what is needed?

Reply | Quote

I have a list of items on a spreadsheet that has several dozen items on a drop down list, then one next to it with quantity.
What I’d like to do is once that pull down is chosen, to be able to total the cost of that item times the quantity in the next drop down into another cell.
Is that possible? if so, it will be extremely helpful.

Thanks!

See attached – Column A has a drop down list, Column B has a lookup to prices, Column C is where quantity is entered and Column D gives Quantity times price.

Regards,
Maria

Reply | Quote

Fantastic! Thanks. That will save me a lot of time. Both of these will work great.

Reply | Quote

OK, now I’m not sure how to edit the drop down to tell it MY cells to calculate. I thought I knew what I was doing, but I’m working at home on a different computer and I must have something turned off or I don’t really know what I’m doing. Can you clarify how to edit it? The other spreadsheet I had, I created the drop down, then protected the sheet for the drop down to work correctly. When unprotected, I just right-clicked the drop down to get to the format control. Were these done differently? Thanks!

Reply | Quote

For my example, if you want to change the Item descriptions, change them in Column A, then the drop down list in Cell F2 will change automatically. If you want to change where the list gets its data from, change the data source.

Hope that helps.

Tim

Reply | Quote

Thanks! Now, can you tell me how to make the pull down to be able to show more lines?

Reply | Quote

I made a couple of changes for ease of use. I gave the items a Range Name of ItemList.
To add new items, just insert rows somewhere within the range named ItemList. I also changed the Validation Source to refer to ItemList.
Results should be if you add items, change item descriptions or change unit costs all calcs for Cells F2 thru I2 should be automatic.

A revised workbook is attached.

Hope that helps

Reply | Quote

Thanks. What I mean is to have the drop down list show more of the list without having to scroll the list.
Is there a way to have it also increase the font size of what it shows in the drop down?

Reply | Quote

No, you would need to have a control on the worksheet for that. See Debra’s page here.

Reply | Quote

Crazy,

Check this out.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

No, you would need to have a control on the worksheet for that. See Debra’s page here.

Crazy,

Check this out.

It seems that both Rory’s and RG’s links go to the same info. Is there any info difference between the two?

Reply | Quote

I’m not figuring this out. I was able to get everything to work correctly with the DataValidation…all except for the further editing (font size, drop down list number, etc.).
I am using Excel 2007 and none of the informaiton matches up with the instructions above on the link.
Am I needing to start over now and use a different method? Sorry, I’m confused.
I have attached what I have so far (blanked out my data for the most part) if someone can help me.

Thanks!

Reply | Quote

I’m not figuring this out. I was able to get everything to work correctly with the DataValidation…all except for the further editing (font size, drop down list number, etc.).
I am using Excel 2007 and none of the informaiton matches up with the instructions above on the link.
Am I needing to start over now and use a different method? Sorry, I’m confused.
I have attached what I have so far (blanked out my data for the most part) if someone can help me.

Thanks!

CrazyK,

The further editing (Font size, Drop Down List Number of rows, etc) only applies to combo boxes – you are only using Data Validation in Drop down Lists not combo boxes. You would need to follow the earlier examples in the earlier links on how to create and format combo boxes.

Regards,
Maria

Reply | Quote

Can combo boxes also do what I’m trying to do with giving the total in another cell?
Is anyone else using Excel 2007? Those instructions are not for that version. I don’t see a “Control Toolbox”. It might be under Insert | Controls. There are two areas: Forms Controls and ActiveX Controls.

One choice will allow some editing of where the drop list comes from and how many rows. The other looks like what they are talking about, but I can’t see how to get to read from any other data for the list. Then when I click on the Design Mode button the list actually goes away. Even on their sample page I can’t figure out how they made that drop down lock to that cell, nor how it reads from any other data.

Reply | Quote

Can combo boxes also do what I’m trying to do with giving the total in another cell?
Is anyone else using Excel 2007? Those instructions are not for that version. I don’t see a “Control Toolbox”. It might be under Insert | Controls. There are two areas: Forms Controls and ActiveX Controls.

CrazyKZ,

You can get to the Control Toolbox in Excel 2007 by choosing the Developer Tab on the ribbon. If you don’t see the Developer Tab you can set this to show in Excel Options.

You should find the Combo Box Control there, sorry I can’t help further as I have not used this feature of Excel 2007 yet.

Regards,
Maria

Reply | Quote

I understand now that I am to click on the drop down cell, then choose DataValidation and tell it the range from there.
However, even in their sample, there is no place to be able to edit the drop down numbers, font, etc.

Reply | Quote

I’ve been playing around with this some. I’ll have a number of users with this form and the double-clicking to bring up the “other” list is very confusing and annoying, so I’ll not be using their script.

In Excel 2007 there are two drop downs available. One is the Form Control. It will only allow you to change the number of rows, but not font size or anything else.
The other is the ActiveX Control. With it I can control all of the functions that I’m looking for (ie: number of rows displayed, font size, etc.)

…still playing with this to see if I can get it to fully work.

Reply | Quote