commission

[nick thompson]

i have two questions that are connected . i want to produce on 4 different
spreadsheets data from one spreadsheet to work out commissions. A consultants
bonus is paid and worked out quarterly.The more fees earnt in a quarter the
higher the bonus. At quarter end the calculation starts again I want one
spreadsheet to capture every sale. So columns one will be "sales date"
,column 2 "revenue", column 3 commission rate, column 4 commission.

Then the other spreadsheets will be quarter periods.So quarter one is
Jan-March, the next spreadsheet is April-June and so

i want to write something in spreadsheet 2 so that it puls from the main
spreadsheets all rows of information that have a sales date 1st Jan-31st
March and then copy the columns.If the date is 1st April ,it sends this
information to Spreadsheet 3 and so on.Then , in the quarter spreadsheets i
can write a if statement to calculate.I am struggling to write something that
copies the data to the correct spreadsheets

i hope that makes sense

 

[Bernie Deitrick]

nick,

There are much better ways to do that.

1 Use one sheet, with a fifth column "Quarter". Enter the correct quarter
for every entry. You should also enter which consultant you have - that
way, you won't need a separate workbook for each consultant. Then, when you
want to see a specific quarter, apply filters to your data to show just that
quarter, for that consultants, and use SUBTOTAL functions to total just the
visible cells.

2 Or use a pivot table based on the data and group the data by quarter and
by consultant - Excel can figure out the quarter based on the date, so you
don't even need to use another column.

3 Use SUMPRODUCT functions to figure out the values - a little more
advanced, so we're not going to confuse you....


HTH,
Bernie
MS Excel MVP