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.

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.

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

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"]

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.