+ Reply to Thread
Results 1 to 10 of 10

Staff Absence Record

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    5

    Staff Absence Record

    Hi Everyone

    I found this wonderful template somewhere in the forum.

    Please assist to enhance it as I desire to have a monthly summary on the "Front Page". That means with the change of month at cell "AJ2", the data from the individual staff worksheet will auto-populate in this summary for that particular month.

    Thanks and regards
    Jane
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,399

    Re: Staff Absence Record

    Hi Janesong and welcome to the forum,

    Your template is very nice looking but almost impossible to accomplish what you want. I got close to a formula on the Staff sheet but failed because of merged cells in the named sheets.

    I did create another way to do this problem. See Sheet 1, where you simply add a row with a Name, Date and Reason. Then you can refresh the Pivot Tables on Sheet 2 and see the result. ALSO - you can filter the reason to any one or multiples you want. You can also sort the dates by month or year and get counts of any of the reasons. I'd suggest you do this problem using a TABLE like I show on sheet 1 and a Pivot Table like Sheet 2.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Staff Absence Record

    Thank you for the assistance.

    However, I do not want the count/number of days on the "Front Page".

    Ideally, I hope to populate the alphabets (representing the type of leave taken) for the individual staff on a monthly basis for comparison and monitoring purposes. As the month change at cell "AJ2", other datas corresponding to that month will auto populate in the summary.

    Thanks and regards
    Jane

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,399

    Re: Staff Absence Record

    Hi,

    I'm so confused . You don't want the total number of days absent for a month or year? On your individual name sheets you are allowed two different letters per day. How is that supposed to show up on the Front Page, when there is only a single cell to display letters?

    Because of merged cells on the named sheets and no merged cells on the Front Page, I failed to get a formula that worked. I did get close.
    Maybe one of the smart Guru's can get you a formula for this problem. OR - maybe you can study the formula I have already on the Front Page and make it work.

  5. #5
    Registered User
    Join Date
    08-22-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Staff Absence Record

    Hi

    I am sorry. I have merge the rows.

    On a monthly basis, I want to view all the staff absense records (type/reason) for a particular month by changing the month on the front page.

    Please help.

    Jane

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,399

    Re: Staff Absence Record

    Look on Sheet1 and Sheet2. I thought a pivot table example might do that for you.

  7. #7
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Staff Absence Record

    Try downloading Attendance History from the site below and see if it is helpful.

    HTML Code: 
    https://sites.google.com/site/normay/excel-files

  8. #8
    Registered User
    Join Date
    08-22-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Staff Absence Record

    Hi MarvinP

    Thank you for the advice. I got what I desire. It works great!

    However, I have one more question. Is there a way to restrict view of individual staff worksheet with a password.

    Which means if anyone open the workbook, only the "Front page" can be view freely and other worksheets require a assword to view it.

    I tried to protect and hide but it cant work.
    Please help. Thanks.

    Jane[COLOR="Silver"]
    Last edited by Janeong; 08-26-2012 at 08:14 PM.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,399

    Re: Staff Absence Record

    You can hide the individual sheets but I don't think you can password protect them from being viewed. You can password protect the workbook.

    I may be wrong. I've searched the net and find some links...
    http://www.ehow.com/how_8228429_pass...cel-sheet.html
    http://office.microsoft.com/en-us/ex...010078580.aspx

    The above may only not allow changing data but I don't know if it keeps others from viewing the data.

  10. #10
    Registered User
    Join Date
    08-22-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Staff Absence Record

    Hi Marvin

    Thank you for the link.

    Jane

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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