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

 

[Iknewthisguy]

Thanks for the help Mick. I've got two issues with this solution:

1) my ultimate goal is to have a percentage fee calculated off the output so I need it to populate a cell
2) I'd like to keep it to the listbox so that it's simply selecting the 2 or 3 items from the list and then you get your total.

You think it's possible?

Chris

 

[BrianExcel]

I think Mick is right, except for the multi-column on a user form. I checked that out and was able to get a multi column going by following the steps I had mentioned...

 

[MickG]

Try this:-
Set up basically as the previous code, But with an ActiveX "ListBox" control from the "View", "ToolBars", "Control ToolBox" Menu.
The list Box will show Columns "A & B"
The results, Will appear when you click in "C1", and will give the percentage selected of the total in the Values range.
If you get the basic thing working , I will alter "Percentage "Details if different.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] ActiveSheet.ListBox1
    .ListFillRange = ""
    .MultiSelect = fmMultiSelectMulti
    .ColumnCount = 2
    .List = Rng.Resize(, 2).value
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oVal As Double
Dim n As Integer
Dim oSum As Double
If Target.Address(0, 0) = "C1" Then
    Target = ""
    With ListBox1
        For n = 0 To .ListCount - 1
            oSum = oSum + .List(n, 1)
                If .Selected(n) Then
                    Target = Target + .List(n, 1) 
                End If
        Next n
    End With
Target = "Av= " & Format(Target / oSum, "0.0%")
Target.Offset(1).Select
End If
End Sub

Regards Mick