Populate calendar and summary

HI all

I'm looking through various threads, but thought i'd post - in case anyone sees a better way for me to get what i'm after.

I'm needing to create a register of leave - each site (location) will have their own register - but then the senior leadership team needs an overall look at the leave.

The Senior team - don't necessarily need names, however they do need to know a summary of how much leave is taken at each site by day / week / month.


On the attached - can the datatable automatically populate details into the LeaveCal2 tab?

I'm trying to future proof this, hence asking before going too much further - as i'm sure once i get this working the management team will want more details added!

Originally Posted by annaisakiwi

HI all

I'm looking through various threads, but thought i'd post - in case anyone sees a better way for me to get what i'm after.

I'm needing to create a register of leave - each site (location) will have their own register - but then the senior leadership team needs an overall look at the leave.

The Senior team - don't necessarily need names, however they do need to know a summary of how much leave is taken at each site by day / week / month.


On the attached - can the datatable automatically populate details into the LeaveCal2 tab?

I'm trying to future proof this, hence asking before going too much further - as i'm sure once i get this working the management team will want more details added!

Hi annaisakiwi,

IMHO, it's not the best choice to make LeaveCalendar sheet as Data Structure.
Because it'll make you a headache to scroll to the right once you reach 20-30 employees or even more (no Columns limit).
And it'll not support for more than one location because one sheet only for one location, since you have 9 Locations then you'll have to build 9 LeaveCalendar.
So it's more likely used as Reporting Form.
Maybe experts, gurus, masters and seniors have another better solution or suggestion for you.

Well, this is my suggestion for you.....
As I said before, because LeaveCalendar not suitable for Data Structure, which is Datatable sheet is the most suitable one.
So you have to fill Datatable manually and carefully. Or if your Data still formatted like in the LeaveCalendar, you may use Excel built-in filter function then copy paste values one by one names.

Once the Datatable created then we'll use LeaveCal2 and LeaveCalendar as Reporting Form.
I've made 2 sheets for each reports, by Formula and by Pivot Table. And some adjustment too for your Data structure to fulfill your Calendar Style Report.

For more details, you may check on my excel file attachment if you wish.
Let me know if you have any question.

Oh wow - not sure how i missed this - but it is brilliant! thanks so much!

I'm defiantely going to build on what you have done - this is brilliant!

The second part of my 'build' is that each fortnight we receive a report from payroll that states the leave balances of each employee - by type of leave.
I want to be able to quickly identify what leave balance someone has - taking into account their current planned leave from the payroll report date through to their anniversary date.

I'm very interested in how you would see this being incorporated

Hi again

Is there any way to change the functionality in the 'Invercargill Leave - Copy' to have the data table have a From and To Date?

Just looking for a way to make this even better than what it currently is.

Thanks