If you want to code in VBA, you can use select case rather than If. Here
is a sample code. But like to want you that I'm beginner myself. Just
like to try some coding.
To see the result, put one command button in your "totals" sheet. Then
execute below procedure with that button. This only work for the first
two person and until Mac only. You can add if you think okey. But I
think there a lot of ways to do much better.
Sub GetMonthlyData()
Dim strName As Variant 'String 'employee Name
Dim strmonth As String 'Month
Dim lngCol As Long 'column number
Dim lngRow As Long 'row number
strName = Range("I2").Value
ClearPreviousData
Select Case strName
Case "Blobby"
'January
lngRow = 6
For lngCol = 2 To 32 'your date column
With Sheets("Jan 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
'February
lngRow = 10
For lngCol = 2 To 32 'your date column
With Sheets("Feb 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
'March
lngRow = 14
For lngCol = 2 To 32 'your date column
With Sheets("Mar 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
Case "Doodle"
'January
lngRow = 6
For lngCol = 2 To 32 'your date column
With Sheets("Jan 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
'February
lngRow = 10
For lngCol = 2 To 32 'your date column
With Sheets("Feb 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
'March
lngRow = 14
For lngCol = 2 To 32 'your date column
With Sheets("Mar 06")
Cells(lngRow, lngCol) =
Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"),
lngCol, False)
End With
Next
End Select
End Sub
Sub ClearPreviousData()
Dim lngRow As Long
For lngRow = 6 To 50 Step 4
Range(Cells(lngRow, 2), Cells(lngRow, 32)).ClearContents
Next
End Sub
--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399447