Display a summary sheet, for several tabs

D

Duncs

I have an Excel 2003 spreadsheet that had 12 tabs on it, one for each
member of the team. On this tab, they record details of specific
calls that they have taken, including the date that the call was taken
on.

What I want to do, is include a summary tab that will show for all
tabs, the date and the summation of all financial values from the
tabs.

So, for example, if the first three tabs show the following
information:

Tab Date Written Off
Original New

1 10/10/2008 250.00
6.00 5.00
1 10/10/2008 175.00
25.00 17.00
1 12/10/2008
6.00 3.05
2 10/10/2008
27.00 15.00
2 15/10/2008 75.00
3 12/10/2008
6.00 5.00
3 12/10/2008
12.00 7.50
3 13/10/2008 125.00
12.00 3.05

I want the front summary tab to show:

Date Total Written Off
Original Total New Total

10/10/2008 425.00
59.00 37.00
12/10/2008 0.00
24.00 15.55
13/10/2008 125.00
12.00 3.05
15/10/2008 75.00
0.00 0.00


Is this possible?

TIA

Duncs
 
D

Duncs

Duncs said:
I have an Excel 2003 spreadsheet that had 12 tabs on it, one for each
member of the team. On this tab, they record details of specific
calls that they have taken, including the date that the call was taken
on.

What I want to do, is include a summary tab that will show for all
tabs, the date and the summation of all financial values from the
tabs.

So, for example, if the first three tabs show the following
information:

Tab Date Written Off
Original New

1 10/10/2008 250.00
6.00 5.00
1 10/10/2008 175.00
25.00 17.00
1 12/10/2008
6.00 3.05
2 10/10/2008
27.00 15.00
2 15/10/2008 75.00
3 12/10/2008
6.00 5.00
3 12/10/2008
12.00 7.50
3 13/10/2008 125.00
12.00 3.05

I want the front summary tab to show:

Date Total Written Off
Original Total New Total

10/10/2008 425.00
59.00 37.00
12/10/2008 0.00
24.00 15.55
13/10/2008 125.00
12.00 3.05
15/10/2008 75.00
0.00 0.00


Is this possible?

TIA

Duncs
Click to expand...
 
D

Duncs

Sorry, I think I had TABs in the last post...re-formatted:

I have an Excel 2003 spreadsheet that had 12 tabs on it, one for each
member of the team. On this tab, they record details of specific
calls that they have taken, including the date that the call was taken
on.

What I want to do, is include a summary tab that will show for all
tabs, the date and the summation of all financial values from the
tabs.

So, for example, if the first three tabs show the following
information:

Tab Date Written Off Original New

1 10/10/2008 250.00 6.00 5.00
1 10/10/2008 175.00 25.00 17.00
1 12/10/2008 6.00 3.05
2 10/10/2008 27.00 15.00
2 15/10/2008 75.00
3 12/10/2008 6.00 5.00
3 12/10/2008 12.00 7.50
3 13/10/2008 125.00 12.00 3.05

I want the front summary tab to show:

Totals
Date Written Off Original New

10/10/2008 425.00 59.00 37.00
12/10/2008 0.00 24.00 15.55
13/10/2008 125.00 12.00 3.05
15/10/2008 75.00 0.00 0.00

Is this possible?

TIA

Duncs
 
D

Don Guillett

The easisest way is to put a sumif formula on the top row of each column
=SUMIF(B3:B100,Summary!A2,C3:C100)
and, in your summary sheet,
=sum(sheet1:sheet3!b1)
=sum(sheet1:sheet3!c1)
etc
 
D

Don Guillett

Or a macro with a list of your dates in a2:a5

Sub getsummary()
Range("b2:d5") = ""
For Each d In Range("a2:a5")
For Each sh In Worksheets
If sh.Name <> "Summary" Then

d.Offset(, 1) = d.Offset(, 1) + _
Application.SumIf(sh.Range("a2:a100"), d, sh.Range("b2:b100"))

d.Offset(, 2) = d.Offset(, 2) + _
Application.SumIf(sh.Range("a2:a100"), d, sh.Range("c2:c100"))

d.Offset(, 3) = d.Offset(, 3) + _
Application.SumIf(sh.Range("a2:a100"), d, sh.Range("d2:d100"))

End If
Next sh
Next d
End Sub
 
D

Duncs

Don,

I don't fully follow your post, sorry!

Let's assume that the individual tabs have the data in columns A -> D. If I
use the formula you suggested in your post, I would place this in a new row
at the top of each tab, and the formula for column B would be as follows:

=SUMIF(A3:A200, Summary!B3, B3:B200)

So, from the help, SUMIF will sum all cells in the range B3:B200, where the
date in the range A3:A200 matches the date that is shown in the cell
Summary!B3. Am I right?

The problem is, I want my summary sheet to show a succesion of dates, so how
would this formula provide this? For matching the first date that is in the
list, it works perfectly but, it doesn't for the others in the list of
dates.

Any advice?

TIA

Duncs
 
D

Duncs

Also, in the Summary sheet you've listed the calculation as:

=sum(sheet1:sheet3!b1)

to show the total for each column, for each day. Each of the tabs has the
name of the team member on it, rather than the default of Sheet1 etc. As a
result, I can't get this part to work either.

TIA

Duncs
 
D

Duncs

Don,

Due to the way my employer has implemented Excel, Windows XP etc. I
can't create any files with macros in them, as the macros will not
run.

Thanks anyway

Duncs
 
D

Duncs

Herbert,

Many thanks for your file...it looks just like what I want. The only
problem, if it is, is I use Excel 2003.

Looking at your spreadsheet, it follows the same basic format as th
eone we have:

1. Each tab is named after the employee
2. There are some additional columns on the sheet, but these do not
matter

Where I am stuck is as follows:

1. In Step 2b of 3, it lists the Ranges as Table1[#All], Table2[#All]
etc. How do you achieve this in 2003?
2. In the input box for 'Field one' you have entered 'AA'. Does this
correspond with the naming of the first data entry tab on your
spreadsheet, and so will correspond with my first one?
3. If this does indeed represent the first entry tab, should you be
able to select it from the drop-down list, or do you just type it in?

When I try to set it up, I'm getting a list of the employees under the
'Row' heading, with the Call Date values being summed in the 'Value'
area of the table. What I really want, is a Pivot Table the same as
the one you have in your spreadsheet, but I am unable to get it.

HELP!!!

Duncs
 
D

Duncs

Herbert,

On your tab 'AA', you have the comment "Select all data and headers and
assign a name to it (ArrayAA)". By this, are you meaning "Select all data
and headers and assign a name to it (ArrayAA), by using the
Insert|Name|Define menu function"?

TIA

Duncs
 
H

Herbert Seidenberg

Yes.
Alternate method:
Select the data and type a name
into the Name Box in the upper left corner.
Hit Enter.
 
D

Don Guillett

Your employer needs to be upgraded. I have sent you a cumbersome formula
file using INDIRECT.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don,

Due to the way my employer has implemented Excel, Windows XP etc. I
can't create any files with macros in them, as the macros will not
run.

Thanks anyway

Duncs
 
D

Duncs

Herbert,

Sorry for the delay in getting back to you...

I've now created the PivotTable and all looks well...however:

1. As the spreadsheet is set to 'Shared', I have to un-share it before I can
update or filter the pivottable. Not really a problem, more of a
hiniderance than anything else.
2. I can't, no matter what I try, get the grouping on the dates / rows to
work. Everytime I try to group the data, Excel warns me "Cannot group that
selection".

Apart form these two areas, the PivotTable is a great idea...if only it
didn't have the two issues above.

Thanks for your help Herbert

Duncs
 
H

Herbert Seidenberg

More than likely, one of your dates is really text.
Format them as General and make sure they
are all numbers.
No experience with Sharing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

basic sum with a variable twist 3
multiple results display after filter function 1
data to summary sheet for a dummy 0
Calculation Question 4
Automatic udates on a summary sheet 3
if > < on one tab - want total on a summary sheet 3
Ready for this? It's a doozy! 4
Ugly file import into Excel 1

Top