Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select from table and listbox
Hello Group,
I've in me Sheet table with: Product, Manager, etc When Manager get new Product in excel is new row for ex: Product Manager Value prodA Ann 10 prodB Ann 2 ProdC Tom 15 prodD Tom 8 prodE Eva 20 prodF Ann 4 prodG Eva 10 prodH Tom 11 This table is upgrade every month (new products and new managers). Value in collumn Product is unique. I've in my sheet ListBox. My questions? 1. How add to ListBox all Managers without duplicates? ListBox for ex: Manager ------- Ann Eva Tom 2. How add in this ListBox collumn Bonus with percent value? Manager Bonus% -------- -------- Ann 10 Eva 8 Tom 5 3.How add new sheet with: Manager Value Bonus% Bounus$ Ann =(10+2+4) from first table 10 (from ListBox) =Value*Bonus% Eva =(20+10) 8 =Value*Bonus% Tom =(15+8+11) 5 =Value*Bonus% More thx Pawel R |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select from table and listbox
This is a bit vague.
You can simply use the Data/Filter / Advanced Filter to extract a list of unique manager names. Extract th emanagers' names to a cell in A below the main table. Now add in B the bonus amounts. We can now use a SUMIF to total the values and multiply by the bonus You don't explain where th ebonus percetage numbers come from. assuming your table is A3:C11 so your manager name is in "C" then add the following FORMULA to the cell to the right of the bonus =SUMIF(B4:B11,A15,C4:C11) * B15 This can be done fairly easily in code. Add the main table to a clean worksheet and range name it Main_Table The following procedure builds a results table with the heading Manager, Bonus and Amount. The Amount is a formula. The bonus is entered as 0. When the table is built, change the bonus from 0 to whatever you want and recalculate the sheet. Sub GetTotals() Dim rSource As Range Dim rTarget As Range Dim lastrow As Long Set rSource = Range("Main_Table") Set rTarget = rSource.Offset(0, rSource.Columns.Count + 1).Resize(1, 1) rTarget = "Manager" rSource.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=rTarget, Unique:=True lastrow = rTarget.End(xlDown).Row Set rTarget = rTarget.Resize(lastrow - rTarget.Row + 1, 3) rTarget.Columns(2) = 0 rTarget.Columns(3).FormulaR1C1 = "=SUMIF(RC[-5]:R[7]C [-5],RC[-2],RC[-4]:R[7]C[-4]) * RC[-1]" rTarget.Resize(1, 2).Offset(0, 1).Value = Array ("Bonus", "Amount") End Sub Workbook available on request Patrick Molloy Microsoft Excel MVP -----Original Message----- Hello Group, I've in me Sheet table with: Product, Manager, etc When Manager get new Product in excel is new row for ex: Product Manager Value prodA Ann 10 prodB Ann 2 ProdC Tom 15 prodD Tom 8 prodE Eva 20 prodF Ann 4 prodG Eva 10 prodH Tom 11 This table is upgrade every month (new products and new managers). Value in collumn Product is unique. I've in my sheet ListBox. My questions? 1. How add to ListBox all Managers without duplicates? ListBox for ex: Manager ------- Ann Eva Tom 2. How add in this ListBox collumn Bonus with percent value? Manager Bonus% -------- -------- Ann 10 Eva 8 Tom 5 3.How add new sheet with: Manager Value Bonus% Bounus$ Ann =(10+2+4) from first table 10 (from ListBox) =Value*Bonus% Eva =(20+10) 8 =Value*Bonus% Tom =(15+8+11) 5 =Value*Bonus% More thx Pawel R . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Multiple Items and return value for Dropdown or listbox | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Problem using INDEX to select items in listbox | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |