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
"nick thompson" <(E-Mail Removed)> wrote in message
news:A51B835D-B6DC-408F-BA4F-(E-Mail Removed)...
>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
> --
> nick
|
Similar Threads
