Search multiple worksheets for employee no.; count leave taken; summarise one worksheet

Worksheet which summaries staff used leave and calculates reaming leave allocation, all values summarised on new sheet [sheet name “SummaryLeaveSheet” ]

Hi all,

I would like help creating a method, formula or VB code which can look through 12 worksheets [named “LARSheet1” to “LARSheet2” ]; find an employee’s pay number on a row (say B25) and return a count of each attendance (“a”); lateness (“L”); holiday (“am or pm”) or Sickness (“S”) record on that row. The row may change on different worksheets but the search method can always use (B25) as a starting point.

With the found values, I would like a total count to be returned on a summary worksheet [“SummaryLeaveSheet”]

I would also like to list the dates each leave was recorded on the various worksheets [i.e “LARSheet1” to “LARSheet2” ] in a table.

Ideally one summery sheet could be used for all employees, the information would update and change based upon a pay number located in a cell linked to a ‘data validation’ drop down list.

Tall order I know….. Any help out there???


Many thanks, moshjosh

Example of layout of [“LARSheet1”]

screenshot_LARSheet1.PNG

===== ======== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======

This is a multi-part project which is collectively known as “Leave, Attendance Management” system using Excel”. . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

‘Leave attendance record’ worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12"]. http://www.excelforum.com/excel-gene...-each-day.html

‘Data validation’ worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet “FormulaListSheet” ].

‘Database’ worksheet acting as a of staff details database. [ “StaffdBaseSheet”].

‘Summary reaming leave’ page (this thread) which shows a summary of staff used leave and calculates reaming leave allocation. [ “SummaryLeaveSheet” ]

A year and two months later and I am happy to say I have built my long slaved over LMS using excel formulas, the new excel table feature in Excel 2010 and VBA. If anyone is interested in seeing my solution, send me a forum message with a bit of a blurb about what you need and I will be happy to share my solution. It would be too long to post as a tread.