Select from table

[Pawe³ Ratajczak]

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
prod A Ann 10
prod B Ann 2
Prod C Tom 15
prod d Tom 8
prod e Eva 20
prod f Ann 4
prod g Eva 10
prod h 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.
PawelR

 

[Dave Peterson]

#1. John Walkenbach has some sample code at:
http://j-walk.com/ss/excel/tips/tip47.htm

That builds a list of unique values using a collection and then sorts that
collection.

#2. I'm not sure how this table is made/updated, but if it's fully defined per
person, then I'm not sure I'd use a listbox to get the bonus percentage.

I think you could use the manager's name from the first listbox and then some
worksheet function to return the value for that manager.

If the table (names and bonuses) were on sheet2, you could use an =vlookup() to
get the bonus precentage.

#3. One way maybe to do Data|Filter|Advanced filter and have the unique values
moved to a new sheet.

Then put another formula in the next column:
=sumif(sheet1!$b$2:$b$99,A2)

And finally just multiply this result by the =vlookup() formula.





And then

 

[Dave Peterson]

I don't know what you're doing with the listbox, but maybe you can apply
Data|Filter|autofilter to your range to hide/show the managers when you want.