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