K
Ken Ivins
I have created a worksheet of new/lost and sales moved from one vendor to
another with the associated sales people.
New
Date Customer Name Type Vendor Premium Commission SalesPerson
-----------------------------------------------------------------------------------------------------------------------------------
Lost
Date Customer Name Type Reason Vendor Premium Commission SalesPerson
--------------------------------------------------------------------------------------------------------------------------------------------------
Moved
Date Customer Name Type Placed With Moved From Premium Premium Premium
Comm % Comm % Change Name
I like to have excel look down a column of new sales and find all of Vendor
"A" sales and return a sum for the year. I need to do this for each vendor,
each sales person, both sales and commission, for new, lost and moved from
one to another.
I have created lists of sales people and vendors to insure that they are
spelled correctly.
I have tried VLookup but that only finds the first time a vendor or Sales
person is encountered. I tried an "If" statement for each row and this works
but I am running out of columns for each variable and future new vendors and
sales people. I could rotate the columns to rows so I could do these "If"
statements but that is a lot of work.
I tried a pivot table but then I need to create a macro with a button or
short cut to update it and sort it alpha by vendor/sales person (on multiple
worksheets). I think I know enough Visual Basic to do this but I know
people will forget to update things and wonder why the information is wrong.
I prefer to keep things as simple as possible for the end user (less is
better).
So is there a better (simpler) function I could use or do I need to learn
more Visual Basic to make this happen?
Thanks,
Ken
another with the associated sales people.
New
Date Customer Name Type Vendor Premium Commission SalesPerson
-----------------------------------------------------------------------------------------------------------------------------------
Lost
Date Customer Name Type Reason Vendor Premium Commission SalesPerson
--------------------------------------------------------------------------------------------------------------------------------------------------
Moved
Date Customer Name Type Placed With Moved From Premium Premium Premium
Comm % Comm % Change Name
I like to have excel look down a column of new sales and find all of Vendor
"A" sales and return a sum for the year. I need to do this for each vendor,
each sales person, both sales and commission, for new, lost and moved from
one to another.
I have created lists of sales people and vendors to insure that they are
spelled correctly.
I have tried VLookup but that only finds the first time a vendor or Sales
person is encountered. I tried an "If" statement for each row and this works
but I am running out of columns for each variable and future new vendors and
sales people. I could rotate the columns to rows so I could do these "If"
statements but that is a lot of work.
I tried a pivot table but then I need to create a macro with a button or
short cut to update it and sort it alpha by vendor/sales person (on multiple
worksheets). I think I know enough Visual Basic to do this but I know
people will forget to update things and wonder why the information is wrong.
I prefer to keep things as simple as possible for the end user (less is
better).
So is there a better (simpler) function I could use or do I need to learn
more Visual Basic to make this happen?
Thanks,
Ken