Complicated Drop down list

[Iknewthisguy]

Is it possible to have a drop down list that I can select multiple items in and have those items vlookup data?

For example:

Permits/Fees: $8,000,000
Soft Cost: $8,000,000
Hard Cost: $38,000,000
Development Fees: $1,100,000
Total Cost: $55,100,000

The title is in column A and the amount is in column B. Now I need to calculate the construction management fee which would be 1% of whatever my selection is. Different people calculate construction management fee off of different things. So I want to be able to select just hard cost and permits/fees or permits/fees and hard cost and development fees or total cost with a drop down menu.

Any ideas? Any help would be greatly appreciated.

Thanks.
Chris

 

[BrianExcel]

As far as loading the drop down box with multiple columns, yes this can be done.

Use either a form control or ActiveX drop down box) depending which one you need.

If you always have the same number of rows and columns populating the drop down, then name the range. For example, if your drop down will always be Rows 1 - 4 of Columns A & B, then you would select A1:B4 and then name it whatever range name you want.

Then you would go to the properties of the drop down box, enter the same range name in "List Fill Range" and change the column count to however many columns you are including. If you have A & B, it would be 2. A B & C would be 3, and so on.

This will allow you to view all your information. once you get this ping back for help on the other part if you still need it.

 

[Iknewthisguy]

What version of Excel are you using? I am not finding "List Fill Range".

 

[BrianExcel]

I am using 2007 but it shouldn't matter. How are you inserting the drop down? Is it through the 'Developer' tab at the top or are you using Data Validation through the 'Data' tab?

 

[Iknewthisguy]

Developer, I'm using form control.

 

[BrianExcel]

Are you doing your work on a Userform? Use ActiveX control if you are working on a spreadsheet...

 

[Iknewthisguy]

Okay. I've got the box with my data in it. What next?

 

[BrianExcel]

Can you describe to me again what the next steps you are trying to do are? I reviewed your original post but I'm not clear on what you are trying to do...

 

[Iknewthisguy]

I have an original table consisting of 4 rows and two columns. The data is something like:

Cost 1 $4,000
Cost 2 $6,000
Cost 3 $8,000
Cost 4 $10,000

I want a listbox where I can select Cost 1,2 and 3 and in my output cell I would like to get the total of $18,000. If that's not possible I'd like to get it to come up with either cost 1, cost 2, cost 3 or the individual dollar amount such as $4,000, $6,000, $8,000.

 

[MickG]

If your Listbox is from the "Forms" toolbar the the below will do what you want.
I do not believe it is posible to have multicolumns in a Forms ListBox, but you can with an ActiveX control.
For this code you will require a "Forms" ListBox and a ActiveX "Commandbutton" on you sheet
To load the code, Right click your sheet , Select "View Code", Vb window appears.
Paste all the code into the window.
Close VB Window.
Make sure you CommandButton Name is the same as the code Commandbutton name.
Change the Data ranges (Shown as "A1 To B10") to suit, in both codes and the ListBox name to suit.
The "Cost Number" data loads into the list box when you "Activate the sheet" and when you have selected the Required cost Number and clicked the command Button , a Msgbox will return the total of the selected items.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Lbox [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Lbox = ActiveSheet.Shapes("List Box 6").OLEFormat.Object
[COLOR=navy]Set[/COLOR] Rng = Range("A1:B10")
    [COLOR=navy]With[/COLOR] Lbox
        .List = ""
        .ListFillRange = Rng.Address
        .MultiSelect = xlSimple
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Private Sub CommandButton2_Click()
Dim Lb As Object, n
Dim Lbox As Object
Dim tot As Double
Dim Rng As Range
Set Lbox = ActiveSheet.Shapes("List Box 6").OLEFormat.Object
Set Rng = Range("A1:B10")
For n = 1 To Lbox.ListCount
    If Lbox.Selected(n) Then
        tot = tot + Rng(n, 2)
    End If
Next n
MsgBox tot
End Sub

Regards Mick