Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default sumif/vlookup

I have multiple worksheets that I need to roll up to a summary worksheet.
Columns include Employee name, Precontract Hrs, Contract hrs, Admin Hrs, etc.
(6 or 7 columns of numeric info). I need to create a new worksheet that will
go into the monthly worksheets and depending on employee name, go to that
rows' column cells and add the numeric values and bring them back to the
summary.

Thanks :)
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default sumif/vlookup

The code below should do everything. The code will create a worksheet (if
one doesn't exist) called Summary. The it will go through the entire
workbook and create the sumary sheet looking and the column headers in row 1
and the employee names in column A. The code will add each sheet hours to
the summary sheet.

Sub MakeSummary()

'create new worksheet call summary
Found = False
For Each Sht In Sheets
If Sht.Name = "Summary" Then
Found = True
Exit For
End If
Next Sht

If Found = False Then
Sheets.Add befo=Sheets(1)
Set SumSht = ActiveSheet
SumSht.Name = "Summary"
Else
Set SumSht = Sheets("Summary")
SumSht.Cells.ClearContents
End If


NewRow = 2
NewCol = 2
'start making summary shet
For Each Sht In Sheets
If Sht.Name < "Summary" Then
LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
LastCol = _
Sht.Cells(1, Columns.Count).End(xlToLeft).Column
For RowCount = 2 To LastRow
Employee = SumSht.Range("A" & RowCount)
Set c = SumSht.Columns("A").Find(what:=Employee, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
SumRow = NewRow
NewRow = NewRow + 1
Else
SumRow = c.Row
End If

For ColCount = 2 To LastCol
ColHeader = Sht.Cells(1, ColCount)
EHours = Sht.Cells(RowCount, ColCount)
'check if header exists in summary sheet
Set c = SumSht.Rows(1).Find(what:=ColHeader, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
SumSht.Cells(1, NewCol) = ColHeader
SumSht.Cells(SumRow, NewCol) = _
SumSht.Cells(SumRow, NewCol) + EHours
NewCol = NewCol + 1
Else
SumSht.Cells(SumRow, c.Column) = _
SumSht.Cells(SumRow, c.Column) + EHours
End If

Next ColCount
Next RowCount
End If
Next Sht

End Sub



"Alberta Rose" wrote:

I have multiple worksheets that I need to roll up to a summary worksheet.
Columns include Employee name, Precontract Hrs, Contract hrs, Admin Hrs, etc.
(6 or 7 columns of numeric info). I need to create a new worksheet that will
go into the monthly worksheets and depending on employee name, go to that
rows' column cells and add the numeric values and bring them back to the
summary.

Thanks :)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP & SUMIF Jock Excel Worksheet Functions 7 April 2nd 23 07:53 PM
Sumif or vlookup dejavu34 Excel Worksheet Functions 1 January 31st 12 03:45 AM
SUMIF - VLOOKUP mcp201 Excel Discussion (Misc queries) 6 October 8th 09 09:00 PM
VLookup & SUMIf Karen53 Excel Programming 2 March 4th 08 01:05 AM
Vlookup vs Sumif Steph Excel Programming 4 July 31st 06 09:32 PM


All times are GMT +1. The time now is 01:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"