I'm completely stuck. I have a spreadsheet that has a worksheet for
each month of the year plus an extra one called TOTALS.

In each month there is one row per employee (there are about 20-30
employees) and the columns represent the days of the month. Its a
spreadsheet to calculate holiday totals.

The idea is: the holdays get entered with a '1' and in the TOTALS list
there's a drop down of the names of the employees. The select their
name and the total appear for each month. I've tried nested IF's but
I'M limited to 7. I don'T have much programming knowlege and I can'T
figure out how to solve this problem.

I attach a zip file containing my example

I'm grateful for any help.


+-------------------------------------------------------------------+
|Filename: holiday.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3749 |
+-------------------------------------------------------------------+

--
karambos
------------------------------------------------------------------------
karambos's Profile: http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=399447

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

Hello karambos, I believe I have a solutition for programing problem. email
me at . Sorry, I'm new to newsgroups. R Hansen.
somewhere in Alaska


"karambos" wrote in
message ...

I'm completely stuck. I have a spreadsheet that has a worksheet for
each month of the year plus an extra one called TOTALS.

In each month there is one row per employee (there are about 20-30
employees) and the columns represent the days of the month. Its a
spreadsheet to calculate holiday totals.

The idea is: the holdays get entered with a '1' and in the TOTALS list
there's a drop down of the names of the employees. The select their
name and the total appear for each month. I've tried nested IF's but
I'M limited to 7. I don'T have much programming knowlege and I can'T
figure out how to solve this problem.

I attach a zip file containing my example

I'm grateful for any help.


+-------------------------------------------------------------------+
|Filename: holiday.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3749 |
+-------------------------------------------------------------------+

--
karambos
------------------------------------------------------------------------
karambos's Profile:
http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=399447

You should not use Excel for this but Access.
1 your data can not be protected good enough (privacy)
2 your using a sheet (for calculating) to store Data
3 in Access you can store up to ten years of holidays easily




--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182
View this thread: http://www.excelforum.com/showthread...hreadid=399447

thankyou all for your efforts.

However, I found that this worked exactly as I wanted it to: call all
names in January 'JanNames' and call all the data JanData. Then use
this formula:

=INDEX(JanData;MATCH(Dropdown;JanNames;0);COLUMN()-1)

to select the row of data you need. I include the finished product


+-------------------------------------------------------------------+
|Filename: holiday2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3758 |
+-------------------------------------------------------------------+

--
karambos
------------------------------------------------------------------------
karambos's Profile: http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=399447

Hello Karambos- Rick here in alaska. Here is some vba code you can try that
will get you the same results as formula's on the excel worksheets. I also
have a complete excel spreadsheet with this code using your examples.

Rick
HTH

Sub FindHolidayData2()

Dim wsk As Worksheet
Dim wsAr As Variant
Dim EmpName As String
Dim x As Integer, TotRow As Integer
Dim iRow As Integer

' Array list of month sheets
wsAr = Array("Jan 06", "Feb 06", "Mar 06", "Apr 06", "May 06", "Jun 06", _
"Jul 06", "Aug 06", "Sep 06", "Oct 06", "Nov 06", "Dec 06")


Application.ScreenUpdating = False ' freeze screen update

Range("ClearTotals").ClearContents ' clear all data on "Totals" sheet
EmpName = Range("DropDown") ' get employee name from dropdown
list

If EmpName = "" Then
MsgBox ("Please select employee name" & vbCrLf _
& " and try again")
Exit Sub
End If

TotRow = 6 ' Totals month row pointer

For x = 0 To 11 ' loop thru each month sheet
Set wsk = Worksheets(wsAr(x)) ' set worksheet object pointer

' find row number that employee name is on
iRow = WorksheetFunction.Match(EmpName, Range("Names"), 0) + 1
' copy employee holiday data from selected sheet
wsk.Range("B" & CStr(iRow) & ":" & "AF" & CStr(iRow)).Copy
' paste employee monthly data in "Totals" sheet
Range("B" & CStr(TotRow) & ":" & "AF" & CStr(TotRow)).PasteSpecial
(xlPasteValues)
TotRow = TotRow + 4 ' point to next month row in "Totals"
Next x
Application.CutCopyMode = False 'Clear the clipbrd

End Sub


"karambos" wrote in
message ...

thankyou all for your efforts.

However, I found that this worked exactly as I wanted it to: call all
names in January 'JanNames' and call all the data JanData. Then use
this formula:

=INDEX(JanData;MATCH(Dropdown;JanNames;0);COLUMN()-1)

to select the row of data you need. I include the finished product


+-------------------------------------------------------------------+
|Filename: holiday2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3758 |
+-------------------------------------------------------------------+

--
karambos
------------------------------------------------------------------------
karambos's Profile:
http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=399447