Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to create table that shows last rolling 30 business daysdifferences on 4 accounts
Hi,
Each day I create a new worksheet which shows the differences for the prior business day on 4 accounts. I would like to be able to see the aggregate differences for the past 30 business days on each account. To make it simple I post a simplified version of this here. "Data Entry" is the part of the worksheet that shows the differences for the prior business day. "Table" is one way I envision getting the totals for each person over the last 30 business days. I have no clue how to program this however. On this day (4/29) I need to update the table. Since this is day 31 the data for March 17 needs to be erased, and April 28 is appended to the end. The revised totals should show John 3, Frank 1, Sally 2, and Roger 1 (since he had a difference today). The next day I will copy this worksheet, calculate the new differences and repeat the process. Thanks to any of you who take the time to look at this. Data Entry: Prepared By: John Smith Today's Date: 4/29 Reconciled For: 4/28 Person Balances Differences John 127,500.00 $- Frank 86,155.55 $- Sally 99,528.27 $- Roger 111411.22 $155.00 ----------------------------------------------------------------------------------------------------------------- Table: John Frank Sally Roger 17-Mar 0 1 0 0 18-Mar 0 0 0 0 19-Mar 0 0 0 0 20-Mar 0 0 0 0 21-Mar 1 0 0 0 24-Mar 0 0 0 0 25-Mar 0 1 0 0 26-Mar 0 0 0 0 27-Mar 0 0 0 0 28-Mar 0 0 0 0 31-Mar 0 0 0 0 1-Apr 0 0 0 0 2-Apr 0 0 0 0 3-Apr 1 0 0 0 4-Apr 0 0 0 0 7-Apr 0 0 0 0 8-Apr 1 0 0 0 9-Apr 0 0 0 0 10-Apr 0 0 1 0 11-Apr 0 0 0 0 14-Apr 0 0 0 0 15-Apr 0 0 0 0 16-Apr 0 0 1 0 17-Apr 0 0 0 0 18-Apr 0 0 0 0 21-Apr 0 0 0 0 22-Apr 0 0 0 0 23-Apr 0 0 0 0 24-Apr 0 0 0 0 25-Apr 0 0 0 0 Total 3 2 2 0 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to create table that shows last rolling 30 business days diff
I assume the total at the bottom of the worksheet are fgormulas like
=sum(B2:B31). You don't want to delete and add rows because it will effect the formulas. Te best was is simply copy rows 3 to 31 to rows 2 to 30. Ten enter your new data in row 31. Try this one line macro Range("A3:A31").Copy _ Destination:=Range("A2") " wrote: Hi, Each day I create a new worksheet which shows the differences for the prior business day on 4 accounts. I would like to be able to see the aggregate differences for the past 30 business days on each account. To make it simple I post a simplified version of this here. "Data Entry" is the part of the worksheet that shows the differences for the prior business day. "Table" is one way I envision getting the totals for each person over the last 30 business days. I have no clue how to program this however. On this day (4/29) I need to update the table. Since this is day 31 the data for March 17 needs to be erased, and April 28 is appended to the end. The revised totals should show John 3, Frank 1, Sally 2, and Roger 1 (since he had a difference today). The next day I will copy this worksheet, calculate the new differences and repeat the process. Thanks to any of you who take the time to look at this. Data Entry: Prepared By: John Smith Today's Date: 4/29 Reconciled For: 4/28 Person Balances Differences John 127,500.00 $- Frank 86,155.55 $- Sally 99,528.27 $- Roger 111411.22 $155.00 ----------------------------------------------------------------------------------------------------------------- Table: John Frank Sally Roger 17-Mar 0 1 0 0 18-Mar 0 0 0 0 19-Mar 0 0 0 0 20-Mar 0 0 0 0 21-Mar 1 0 0 0 24-Mar 0 0 0 0 25-Mar 0 1 0 0 26-Mar 0 0 0 0 27-Mar 0 0 0 0 28-Mar 0 0 0 0 31-Mar 0 0 0 0 1-Apr 0 0 0 0 2-Apr 0 0 0 0 3-Apr 1 0 0 0 4-Apr 0 0 0 0 7-Apr 0 0 0 0 8-Apr 1 0 0 0 9-Apr 0 0 0 0 10-Apr 0 0 1 0 11-Apr 0 0 0 0 14-Apr 0 0 0 0 15-Apr 0 0 0 0 16-Apr 0 0 1 0 17-Apr 0 0 0 0 18-Apr 0 0 0 0 21-Apr 0 0 0 0 22-Apr 0 0 0 0 23-Apr 0 0 0 0 24-Apr 0 0 0 0 25-Apr 0 0 0 0 Total 3 2 2 0 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to create table that shows last rolling 30 business days diff
I only copied column A. You need to copy columns A to E
Range("A3:E31").Copy Destination:=Range("A2") " wrote: Hi, Each day I create a new worksheet which shows the differences for the prior business day on 4 accounts. I would like to be able to see the aggregate differences for the past 30 business days on each account. To make it simple I post a simplified version of this here. "Data Entry" is the part of the worksheet that shows the differences for the prior business day. "Table" is one way I envision getting the totals for each person over the last 30 business days. I have no clue how to program this however. On this day (4/29) I need to update the table. Since this is day 31 the data for March 17 needs to be erased, and April 28 is appended to the end. The revised totals should show John 3, Frank 1, Sally 2, and Roger 1 (since he had a difference today). The next day I will copy this worksheet, calculate the new differences and repeat the process. Thanks to any of you who take the time to look at this. Data Entry: Prepared By: John Smith Today's Date: 4/29 Reconciled For: 4/28 Person Balances Differences John 127,500.00 $- Frank 86,155.55 $- Sally 99,528.27 $- Roger 111411.22 $155.00 ----------------------------------------------------------------------------------------------------------------- Table: John Frank Sally Roger 17-Mar 0 1 0 0 18-Mar 0 0 0 0 19-Mar 0 0 0 0 20-Mar 0 0 0 0 21-Mar 1 0 0 0 24-Mar 0 0 0 0 25-Mar 0 1 0 0 26-Mar 0 0 0 0 27-Mar 0 0 0 0 28-Mar 0 0 0 0 31-Mar 0 0 0 0 1-Apr 0 0 0 0 2-Apr 0 0 0 0 3-Apr 1 0 0 0 4-Apr 0 0 0 0 7-Apr 0 0 0 0 8-Apr 1 0 0 0 9-Apr 0 0 0 0 10-Apr 0 0 1 0 11-Apr 0 0 0 0 14-Apr 0 0 0 0 15-Apr 0 0 0 0 16-Apr 0 0 1 0 17-Apr 0 0 0 0 18-Apr 0 0 0 0 21-Apr 0 0 0 0 22-Apr 0 0 0 0 23-Apr 0 0 0 0 24-Apr 0 0 0 0 25-Apr 0 0 0 0 Total 3 2 2 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a 12 month rolling average | Excel Worksheet Functions | |||
I need a simple excel-based template for business accounts | Excel Discussion (Misc queries) | |||
create a rolling sum?? | Excel Worksheet Functions | |||
How Do I create a table in excel that shows frequency? | Charts and Charting in Excel | |||
How do you create a vendor tracking form for accounts payable pro. | Excel Discussion (Misc queries) |