hello there
i have a problem if anybody could solve.
i have 9 departments each having approx 100 people, each department is on separate sheet each sheet has following columns.
name, leave_from, leave_to, type_of_leave, days_of_leave. present/leave

now what i am trying is if person is on leave his name should appear on separate sheet, the name should appear when on leave and get removed when the person has returned from leave. that is i get list all person on leave on one sheet.

regards

i have posted this on http://www.mrexcel.com/forum/showthread.php?t=550271

I was going to explain how to do this, but had more fun writing the macro myself instead. The attached has a button on the front "Master" sheet which, when clicked, kicks off a macro that trawls through the other sheets which are expected to be Department worksheets, and gathers the data for anybody marked as "Leave".

I have annotated the macro with enough comments that should make it obvious what is going on if you want to make any changes to this yourself.

Although it may not be as smart as what some of those clever cookies on MrExcel will come up with, at least I got in there before them...

If there are any issues or changes you'd like to see, please let me know.

NB: This macro has been written in Excel 2007. I may be able to get a version for 2003 if required - though have no way of testing it.