Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission Spreadsheet Help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission Spreadsheet Help
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 abturbo wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission Spreadsheet Help
Thanks Ryan
" wrote: 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 abturbo wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission Spreadsheet Help
abturbo wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a 2-3 page Excel spreadsheet as an object in MS Wo | Excel Discussion (Misc queries) | |||
Spreadsheet shortcuts not working | Setting up and Configuration of Excel | |||
Spreadsheet merging problems | Excel Worksheet Functions | |||
commission spreadsheet | Excel Worksheet Functions | |||
Calculating commission schedule (will pay for help!) | Excel Worksheet Functions |