Annual Leave record

[Laguna11]

Hi!

I'm trying to design a spreadsheet to keep tabs on employee annual leave for 30-40 members of staff. I have a worksheet with a calendar (one day per column starting from column E). Columns A-D are name of employee, section, annual leave entitlement and remaining leave. There is one employee per row starting from Row 5. The remaining leave is calculated automatically by looking at the actual days and deducting them from the opening balance.

I then have a separate sheet for each employee. This sheet just has a leave start date, end date and days taken. It then subtracts the days taken from the opening balance. This is a requirement from our HR section.

My question is; is there any way of linking the calendar sheet with the individual record sheets so that leave entries only need to be entered once?

PS Leave is only taken in full days or half days.

Many thanks

 

[borgem]

have you tried using a vlookup in the individual sheets? you can use =vlookup(employeeName,masterArray,columnIndex,False)

where employeeName must be spelled identically as it in the main sheet with quotations around it, masterArray is the main sheets data array with the column containing employee names as the far left column, columnIndex is the number of the column containing the information you wish to pull over (if the names are not in column A, then you must count the name column as you "1" column for this reference)