+ Reply to Thread
Results 1 to 6 of 6

Populate calendar and summary

  1. #1
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    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!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-03-2020
    Location
    Surabaya, Indonesia
    MS-Off Ver
    2016
    Posts
    21

    Re: Populate calendar and summary

    Quote Originally Posted by annaisakiwi View Post
    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Populate calendar and summary

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

  4. #4
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Populate calendar and summary

    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

  5. #5
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Populate calendar and summary

    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

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,282

    Re: Populate calendar and summary

    Perhaps this will help.
    On the LeaveCal2 sheet the 'From' date is placed in cell G2 and the 'To' date is placed in cell I2
    Cells C6 and to the right are populated using: =IF(B6< $I2,SUM(B6,1),"")
    Cells B5 and to the right are populated using: =IF(B6="","",B6)
    A column is added on the Datatable sheet, to identify those names that need to be listed on the LeaveCal2 sheet, which is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The names on the LeaveCal2 sheet are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calendar/Scheduling Sheet Summary
    By mooshoo in forum Excel General
    Replies: 3
    Last Post: 05-02-2018, 01:49 AM
  2. [SOLVED] Populate Summary Sheet
    By ExcelSpreads in forum Excel General
    Replies: 3
    Last Post: 04-20-2016, 11:31 PM
  3. [SOLVED] Populate data for Summary
    By omegaboost in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-02-2016, 03:12 AM
  4. [SOLVED] Assistance with Summary of Employee Non Project Calendar
    By Cidona in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 01-11-2015, 05:18 PM
  5. Replies: 2
    Last Post: 04-01-2014, 09:24 PM
  6. Excel 2007 : Summary for calendar
    By darkhangelsk in forum Excel General
    Replies: 0
    Last Post: 09-22-2011, 11:00 AM
  7. Replies: 0
    Last Post: 04-25-2011, 04:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1