XL 2010: Keeping Running Totals

[ladyck3]

I am having a COMPLETE and utter mind melt going on....
I am trying to create a workbook "Template" that shows Accumulative Totals... caveats...

1. This will be a weekly report, new file each week.
2. On table will be the input table for the previous week (its a Monday report done on the previous Sun-Sat
3. A running Total or Accumulative total week over week. (May do a Rollup to Monthly / Quarterly / Half / Year) RIGHT NOW, need to set up Weekly.
4. There are 2 reports, one for Topic A and another Topic B that are combined for the weekly total, then added to Accumulative to update it.

Things runnining through my head...

How do I add the week input to the existing totals? Because when I change the weekly input to the new week, it will change the accumulative.
I'm thinking I need a hidden table somewhere to match the accumulative but how to make it stay put ... oh I just cannot figure out how to do this.

I have 2.5 days (Wed, Thurs and 1/2 of Friday) to get this completed for each week of the year to present. The data is pulled but this is my LAST HURRAH! I'm retiring on Friday and won't be here after to update or whatever so I need help quickly.

This just came up last Thursday and it took a day to pull the YTD numbers and organize it....

I know there will be clarification questions I just know it. NO VBA PLEASE because I do not understand VBA and neither does my replacement.

I believe this will be hidden data somewhere.

PLEASE HELP!!!


ladyck3
aka: Laurie

 

[SkipVought]

Laurie,

New file each week? Does that mean that someone supplies you with new data? Can that be appended to your table each week?

You state that you want running totals. Does that mean that as you add data thru the week you want to see a column that shows the total as of any particular date, like a bank balance, or are you simply interested in the current total for any week in question?

In order to easily get YTD, MTD, WTD totals, you really need ALL the data in one table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

We pull a weekly history report (its the name of the report Topic A History...
There are agents listed and actions taken... we are establishing who does what.. how many Updates Agent 1 did, how many agent 2 did such as Insert, Update, Delete, etc.

The report will be weekly, and will show the current weekly data (the input) and next to that an accumulative of week 1, + Wk1, + Wk3, etc
You are probably saying we should keep the weekly pages? Then the file will get too large.

I thought, if we show the current week's raw data, have a Pivot table do the counting, then manually enter in a Topic A table the activity of each and another for Topic B. NOTE: SCRATCH MONTHLY/QUARTERLY/HALF

Then add the topics together, trying to show a visual:

Topic A - Weekly data Input Accumulative of Topic A

Topic B - Weekly Data Input Accumulative of Topic B

YTD Accumulative (A + B)

This will be saved and sent as wk#_History
Then next week, open the previous week's file and remove the weekly data from that week and enter the new data for the new week being reported.

Now that's my vision but I just can't wrap my head around it because when I remove the data to enter the new data..... when I remove the data to enter the new data, the accumulative will be less the previous week's info... so in all actuality, the numbers never accumulate.

That's why I'm here... this will not work in this manner so can anyone please recommend a better way?
I thought of creating a hidden area with the accumulative... I could do that... replicate the page and hide it actually and each week on the duplicate... do a copy > Paste Values so the facing page formulas will be adding from the hidden page.. would THAT work?

I'm so stressed over this.... grrrrr. OF course... I'm always stressed when I have to come here... its because I truly have fought to figure it out .. so I'm at frustrated already. I know I'll get the help that I need, this forum has NEVER let me down... I just hate getting to that "UNCLE" point.... and I'm there

Thanks!


ladyck3
aka: Laurie

 

[SkipVought]

For instance, here's a mock up table (Structured Table named Table1)

Date	Amount	Topic	Qtr
1/5/2013	100	A	1
1/29/2013	250	A	1
2/3/2013	230	A	1
2/15/2013	150	A	1
3/3/2013	75	A	1
3/10/2013	320	A	1
4/4/2013	40	A	2
4/15/2013	75	A	2
5/5/2013	60	A	2
5/15/2013	75	A	2
1/5/2013	300	B	1
1/29/2013	100	B	1
2/3/2013	400	B	1
2/15/2013	500	B	1
3/3/2013	300	B	1
3/10/2013	200	B	1
4/4/2013	100	B	2
4/15/2013	700	B	2
5/5/2013	600	B	2
5/15/2013	500	B	2

summary data

.........A	B
ytd	1375	3700
qtd	250	1900
mtd	135	1100

Quarter formula
[tt]
=INT((MONTH(Table1[[#This Row],[Date]])-1)/3)+1
[/tt]
ytd formula in G2
[tt]
G2: =SUMPRODUCT((YEAR(Table1[Date])=YEAR(TODAY()))*(Table1[Topic]=G$1)*(Table1[Amount]))
[/tt]
qtd formula in G3
[tt]
G3: =SUMPRODUCT((YEAR(Table1[Date])=YEAR(TODAY()))*(Table1[Qtr]=INT((MONTH(TODAY())-1)/3)+1)*(Table1[Topic]=G$1)*(Table1[Amount]))
[/tt]
mtd formula in G4
[tt]
G4: =SUMPRODUCT((YEAR(Table1[Date])=YEAR(TODAY()))*(MONTH(Table1[Date])=MONTH(TODAY()))*(Table1[Topic]=G$1)*(Table1[Amount]))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

OH good heavens... I'm going to have to take some time to digest this, f'real.
Just opened the file to start working on it again.

I'm probably going to have a bunch of questions (WHO.. ME?) LOL

Thanks!

ladyck3
aka: Laurie

 

[ladyck3]

As I'm trying to digest this, there is one important piece that I guess I did not explain enough...

We pull a weekly history report (its the name of the report Topic A History...
There are agents listed and actions taken... we are establishing who does what.. how many Updates Agent 1 did, how many agent 2 did such as Insert, Update, Delete, etc.

This is a report that shows

Weekly Input (Wk 2) (TOPIC A)
Content Creator Delete Insert Update Grand Total (by Agent)
Agent 1 1 2 11 14
Agent 2 7 60 1 68
Agent 3 0 22 50 72


Accumulative (TOPIC A)
Content Creator Delete Insert Update Grand Total (by Agent)
Agent 1 2 22 41 65
Agent 2 14 60 1 75
Agent 3 0 82 0 104

So Weekly Input shows the data for Week 2
But the Accumulative shows Week 1 + Week 1
Then Wk3, remove the numbers in the Weekly Input Table and enter the new ones for Week 3
How do I get the Accumulative table to add the numbers for Week 3 to the existing numbers which is wk1+wk2

Repeat Tables for TOPIC B

The 5th Table is the sum of both Accumulative tables.

But its by agent and then weekly totals

I'm so Flippin confused. By the formulas provided it does not include the next level of by Agent.

Have I taken on the impossible? (STILL WORKING ON THIS) :/ While we are discussing.

ladyck3
aka: Laurie

 

[SkipVought]

Where is your SOURCE DATA? Sample?

Your summaries are inconsequential to the process. The SOURCE DATA is essential!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Columns H I J are as follows:

Agent - Action - Action II

I am using Agent and Action II


(Action has multiple data for the same action so I keep the original ACTION but in ACTION II I FIX it so for example...
COPIED from Article0001
COPIED from Article0301
COPIED from Article0877
COPIED from Article4586

So in ACTION II, these individuals all are changed to Copy_Created

That is the only data used form the report that is 10 fields across and can be various number of records even into the thousands,, or close to a thousand.

Are we getting any close?

ladyck3
aka: Laurie

 

[SkipVought]

and the DATE? Where is that?

Can you post a sample of the source data that represents several weeks of data?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

I'm about to cry... I don't understand the forumlas

=INT((MONTH(Table1[[#This Row],[Date]])-1)/3)+1

What do I use for #This Row?? When I enter the row number it just give me errors and won't let me save the formula.
I'm so frustrated at this point. I created a table (I never really got the hang of the use of tables as this is your favorite tool however) I just don't the fact that I have no control over the formatting (I don't think) No time to learn that now.

I'm trying another approach as well... pasting values each week into a second page and have the accumulate table add the values from the separate sheet (total from the previous week) to the new data.

Like I said, I'm not as concerned right now for Quarterly etc, that was not requested so let the next person figure that out I guess... "IF" they ask for it.

Thanks for your patience. I'll be around another 35 minutes and POOF gone til tomorrow
(3:10 PM CDT) currently.

ladyck3
aka: Laurie

 

[SkipVought]

It is a Structured Table reference (My Structured Table is Named Table1)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]