Accumulative calculations

[tacnola]

Greetings,

I need assistance in creating a formula that will allow me to keep columns that accumulate daily.

Example:
In one workbook I get the totals from our daily business. Then on a separate workbook I create a weekly total and on a third workbook I have the monthly. I would like to have the weekly and the monthly update automatically based on the daily totals but I cannot figure out the formula that will allow me to do this.

Please help me

Thank you


Tacnola

[tacnola]

Did I not give enough Information? This is my first time here, so I'm not sure how the forum works. If any one could help me I would greatly appreciate it.

If I need to provide further information please let me know.

Thank you

Teresa

[olasa]

This is what I would do:


Workbook1: Daily Data
Workbook2: Weekly total
Workbook3: Monthly total

1. Open All Workbooks
2. Start in Workbook2.
3. Goto menu: Data>PivotTable and PivotChart Report...
4. Click Next and then select Workbook1 and select your DailyData table - that is needed to make the Weekly Total.
5. Click Next select Layout... (you can display either All weekly totals or just the last weekly total?) Drop Week as Row or as Page). Then OK.
6. Select where you want to put your PivotTable
7. Done
...then do the same procedure for Workbook3 Monthly total.

But to update the PivotTable you must mark the PivotTable and choose Data>Refresh Data

Ola Sandström


Note:
If you don't have column with WeekNumber and if you want a MonthNum. Here's the formulas:
=WEEKNUM(Date,2) ... 2=Week starts with a Monday --> WeekNum 1..52
Either Excels's datavalue can be formated to show just Month, but if you want the MonthNumber:
=MONTH(Date) --> 1..12

[tacnola]

Thank you for your help,

In my workbook 2, the weekly report, the daily totals are added for one week and is placed in one column, then the following week is placed in the second column and so forth until the month is complete then I close this workbook out and start a new workbook. With the pivot table is there a way to do this?

Teresa