Commission Spreadsheet Help

[Guest]

I use excel for a commission spreadsheet. How can I carry values over from
one sheet to the other if they fall into a particular value. For instance I
utilize column D for dates and column E for outstanding commissions due on
those particular dates. On another sheet I would like to tabulate total
outstanding commissions for a particular month that fall within a particular
date in coulumn D on sheet 1

Thanks

 

[ryan.c.christiansen]

Here's one approach:

On the second sheet, use column A for Month, B for Date, and C for
Commission.

1. In column A (Month), use the formula

=MONTH(Sheet1!D1)

to capture the value (from 1 to 12) for the month in the date in D1.


2. In column B (Date) use the formula

=Sheet1!D1

to recapture the date itself from Sheet1


3. In column C (Commission) use the formula

=Sheet1!E1

to recapture the commission from Sheet1


4. Use the fill handle to fill the rest of columns A, B, and C on
Sheet2

Be sure that you have labels in the first row of each column on Sheet2
(Month in column A, Date in column B, Commission in column C)

5. Select one of the cells in the three-column array and choose Data,
Filter, Autofilter. This will place filters in the heading cells for
the Month, Date, and Commission columns.

6. Select one of the cells in the three-column array and choose Data,
Subtotals to open the Subtotal dialog box. Choose At each change in
Month, Use function Sum, Add subtotal to Commission, and click OK.

Now you will have a list that you can filter by Month, Date, or
Commission amount, and you will have commission subtotals by month.

Is that what you're looking for?

-Ryan

 

[Guest]

Thanks Ryan

Here's one approach:

On the second sheet, use column A for Month, B for Date, and C for
Commission.

1. In column A (Month), use the formula

=MONTH(Sheet1!D1)

to capture the value (from 1 to 12) for the month in the date in D1.


2. In column B (Date) use the formula

=Sheet1!D1

to recapture the date itself from Sheet1


3. In column C (Commission) use the formula

=Sheet1!E1

to recapture the commission from Sheet1


4. Use the fill handle to fill the rest of columns A, B, and C on
Sheet2

Be sure that you have labels in the first row of each column on Sheet2
(Month in column A, Date in column B, Commission in column C)

5. Select one of the cells in the three-column array and choose Data,
Filter, Autofilter. This will place filters in the heading cells for
the Month, Date, and Commission columns.

6. Select one of the cells in the three-column array and choose Data,
Subtotals to open the Subtotal dialog box. Choose At each change in
Month, Use function Sum, Add subtotal to Commission, and click OK.

Now you will have a list that you can filter by Month, Date, or
Commission amount, and you will have commission subtotals by month.

Is that what you're looking for?

-Ryan

 

[Franz Verga]

I use excel for a commission spreadsheet. How can I carry values over
from one sheet to the other if they fall into a particular value. For
instance I utilize column D for dates and column E for outstanding
commissions due on those particular dates. On another sheet I would
like to tabulate total outstanding commissions for a particular month
that fall within a particular date in coulumn D on sheet 1

Thanks

I think it would be better if you could post some example data or if you
could upload an example file to www.savefile.com

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy