presenting data on multiple sheet on one consolidated sheet

  • Thread starter Thread starter Jammings
  • Start date Start date
J

Jammings

Are there any formula That can be used to pick up the totals on individual
sheets and show it in a tabular form on one sheet eg if each sheet
representing a department has expenses, say light, water, telephone for
twelve months. All I need is the total for the year for each expense for each
department on one consoludated or summary sheet. The expenses are on
identical rows for each department.

The final product should look like.
HR IT Operations

Light 100 150 200
Water 50 75 100
Telephone 80 120 130
 
I think this is what you want. Create a sheet named 'Summary'. Add two
command buttons. Link the first button to this macro:

Sub ListSheets()
Rows("3:101").Select
Selection.ClearContents
Range("A1").Select
Dim rng1 As Range
Dim I As Integer
Dim sh As Worksheet
Dim blnReplace As Boolean
Set rng1 = Range("A3")
For Each Sheet In ActiveWorkbook.Sheets
If (Sheet.Name) <> "Summary" Then
blnReplace = False
rng1.Offset(I, 0).Value = Sheet.Name
I = I + 1
End If
Next Sheet
End Sub

That lists all the sheets in your workbook in one vertical column. Now,
link the second button to this macro:
Sub ListData10()
Dim ws As Worksheet
Dim rCopy As Range
Dim rDest As Range

Set rDest = ActiveWorkbook.Worksheets("Summary").Range("B3")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Summary" Then
rDest.Offset(0, -1).Value = ws.Name
With ws.Range("B39:T39")
rDest.Resize(1, .Columns.Count).Value = .Value
End With
Set rDest = rDest.Offset(1, 0)
End If
Next ws
End Sub

That copies/pastes all data from all sheets, not named 'Summary', from
B39:T39, on each sheet. I seriously doubt your data is on row B39:T39 on
each sheet, but just change the range to match the rows that have the data
that you want to see in the summary sheet.

HTH,
Ryan---
 
=SUM(Sheet1:Sheet12!cellref) where cellref is the cell on each sheet that
holds the total for that sheet for HR

Same for IT and Operations.


Gord Dibben MS Excel MVP
 
Back
Top