Summary sheet for multiple sheets (difficult problem)

[Z Man]

Here's the scenario: people owe money to companies. We assign each of these
people a number, and create a separate sheet for each of the many companies.
We need to compile a summary sheet (within the same workbook). Here's a
sample sheet (let's call it Sample Company, which is the name of the sheet):


Name ID # Amount

John Jones 555 500
Jim Smith 123 175
Mary Long 232 100


Thus far the workbook has about twenty sheets (not counting the summary
sheet) each named after a company and additional sheets are added frequently
(and sorted). So, Mary Long may appear in fifteen sheets, Jim Smith in one
sheet, etc. We would like the summary to look like:

Name Company Amount

John Jones Sample Company 500
John Jones Green Company 400
John Jones Orange Company 300
TOTAL 1200

Mary Long Sample Company 100
Mary Long Green Company 400
TOTAL 500

So, you can see that the summary contains the detailed data, but all on one
sheet. Let's assume that the ID# and amount are always in the same columns
(I assume that would have to be the case), but we do add sheets for new
companies, and would like those included in the summary sheet.

This appears to be a difficult problem, especially with respect to new
sheets. Can anyone help me solve it?

 

[Ron de Bruin]

Maybe you can use this Z Man

You can use this example
http://www.rondebruin.nl/copy2.htm
To add all information on one sheet

Then use a function to sum your total on that sheet

 

[Z Man]

Maybe you can use this Z Man

You can use this example
http://www.rondebruin.nl/copy2.htm
To add all information on one sheet

Then use a function to sum your total on that sheet

I don't quite know how to apply these solutions to my problem. In my case, I
need to select particular cells based upon the value in another cell in that
row (see my original post for details). Worse yet, if additional worksheets
are set up later, qualifying data on the news sheet(s) must be included.

I don't have the requisite level of Excel expertise to apply your macros to
my particular problem.

 

[Ron de Bruin]

Hi

I hope I understand you correct.
If not Sorry

If you use this example.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA
Sub Test3_Values()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
With sh.UsedRange
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With
End If
Next
Application.ScreenUpdating = True
End SubFunction LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End FunctionA sheet will be add to the workbook with the name Master and all information from
all sheets will be in there

You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
=SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
Change the range to yours

 

[Ron de Bruin]

Sorry for the line wrap
This is better to read
*******************************

Hi

I hope I understand you correct.
If not Sorry

If you use this example.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

Sub Test3_Values()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
With sh.UsedRange
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With
End If
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

A sheet will be add to the workbook with the name Master and all information from
all sheets will be in there

You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
=SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
Change the range to yours

 

[Otto Moehrbach]

Z
I take it that new people are added as well as new companies (sheets).
And I think that you want the Summary sheet updated once in a while. Such a
flux in data will necessitate that you use macros (code).
If you wish, send me a small file with a few company sheets and a
Summary sheet. Include as much explanation as you can. Also tell me the
version of Excel you are using. Send this to me direct via email. Please
don't attach a file to a newsgroup post. Remove "cobia97" from my email
address or it will go nowhere. I'll look at what you have and what you want
to have and you and I will come up with something you can use, hopefully.
HTH Otto

 

[Ron de Bruin]

You need this function also

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

See the website for information
http://www.rondebruin.nl/copy2.htm