Combining workbooks to create individual calendars for employees leave (year)

[kevinonearth]

Hi,

I have a flexi-leave sheet workbook for 4 employees (fictitious names):


Kevin Reynolds: https://dl.dropbox.com/u/90990975/Question/Flexi%20Time%20Sheet%20Kevin%20Reynolds.xlsm


Mary Murphy:

https://dl.dropbox.com/u/90990975/Question/Flexi%20Time%20Sheet%20Mary%20Murphy.xlsm


John Ryan

https://dl.dropbox.com/u/90990975/Question/Flexi%20Time%20Sheet%20John%20Ryan.xlsm


Mickey Spillane

https://dl.dropbox.com/u/90990975/Question/Flexi%20Time%20Sheet%20Mickey%20Spillane.xlsm


Each sheet in each of these workbooks represents one working week, note each week is defined as a table.


Employees choose from a variety of leave options each week


I then want this information to transfer in to the relevant date in the 'Master View' workbook:

https://dl.dropbox.com/u/90990975/Question/Master%20View.xlsx


So that as manager I can see at a glance who is in work and who is not in work.

So if one of the 4 employees selects annual leave on 01/01/2013 in their personal flexi time sheet, I want this to show in the Master View.


Obviously I will expand it to include all employees.


I am not expecting anyone to do my work for me, but if you could point me in the direction that I should be taking then I would very much appreciate it.


Thank you

 

[Luke M]

If you'll check out the Copy/Paste/Merge section on this page:

http://www.rondebruin.nl/tips.htm


You should see several articles about creating a master workbook from several other workbooks. I think this would be the best place to start.

 

[SirJB7]

Hi, kevinonearth!


Give a look to this file:

https://dl.dropbox.com/u/60558749/Combining%20workbooks%20to%20create%20individual%20calendars%20for%20employees%20leave%20%28year%29%20-%20Master%20View%20%28for%20kevinonearth%20at%20chandoo.org%29.zip


It has your 5 original files slightly renamed (if not, I wouldn't find any workbook in "the" folder) as follows:

a) your master view

- changed to .xlsm

- Sheet1, added cyan button to perform data update and retrieval

- Sheet1, new named ranges (EmployeeList, LeaveList, LiveSummary and LeaveDetail), just for easy naming

- Sheet2, added workbook list (named range WorkbookList)

b) your individual files

- first sheet (the only one unprotected) , changed D4 formula from:

=EXTRAE(CELDA("filename";A1);ENCONTRAR("]";CELDA("filename";A1))+1;256) -----> in english: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

to:

=FECHANUMERO(EXTRAE(CELDA("filename";A1);ENCONTRAR("]";CELDA("filename";A1))+1;256)) -----> in english: =DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256))

- changed sheet names from "January" to "Enero" and so on


So you have to:

1) keep master view workbook

2) update your individual workbooks with previous formula in cell D4 of each sheet

3) don't care of anything else in individual workbooks, discard them


Give it a try, fill your employees files with valid data, press the cyan button and then sum manually to check output.


If any of the individual workbooks is close, it's opened and remains like that after execution (i.e., all 5 files open).


Just advise if any issue.


Regards!

 

[kevinonearth]

Thanks, just back in work, I'll take a look,


PS password to unprotect is flexi

 

[kevinonearth]

Hi there Sir,

that's really cool what you did and will be a great help

However I think my question was unclear.


What I am seeking to do is create a very simple summary sheet.

Each individual's time built up will remain in their own worksheet, but their leave will transfer to the master worksheet, so that if for example Mary Murphy selects Annual Leave for 01/01/2013 and 02/01/2013 and Kevin Reynold's is taking certified sick leave on the same days then the master work sheet will look like this:


https://dl.dropbox.com/u/90990975/Question/Master%20View.xlsx


Below the names is the key to what the abbreviations stand for

Annual Leave: AL

Certified Sick Leave: CSL

 

[SirJB7]

Hi, kevinonearth!


Well, that's a different viewpoint, but I have many questions/doubts about it:

a) what if an employee takes two leaves in the same day? e.g., arriving late and exam

b) what is the leave list in rows 8:32 (and 33 for unspecified leaves, as in the different workbooks it isn't a mandatory entry cell) intended for?

c) how to measure the proportional incidence of each leave (for same employee) in rows 2:5? (same problem in my workbook)


Regards!

 

[kevinonearth]

Hi Sir,

a person's hours will be recorded so if a person for example takes a half day to do an exam conditional formatting can be used to shade a cell in proportion to the amount of hours worked.

The capacity is already there to proportionally shade a cell in the conditional formatting menu, isn't it?

 

[SirJB7]

Hi, kevinonearth!

Yes, it can be done, but I don't even want to think about the formulas used for that kind of CF...

I'm led to reformulate first a) question to "what if an employee takes more than one (two, three...) leaves in the same day?".

Despite of that, of course you're free to choose the path towards you're going. If coloured cells instead of accumulated time, go on, but... lots of CF, I guess. Good luck with the combinations.

Regards!

 

[kevinonearth]

ok thanks

the colours are not important the most important is that if leave is taken, it is recorded on the day taken, I'll worry about half days etc if I can solve the problem of transposing a person's leave from their sheet in to the Master sheet