Select from table and listbox

  • Thread starter Thread starter PawelR
  • Start date Start date
P

PawelR

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
 
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
 
Back
Top