Simple yet so hard. I need some help.

I am in the Navy and I am trying to make a muster report for my command. We have nearly 20 departments and personnel transfer back and forth between these departmens so quickly and so often that it makes keeping an accurate real time muster report nearly impossible.

However I have started making one with my very limited knowledge of excel and I could use some help.

I have made identical sheets for each department with columns for all of the required info such as who was present at quarters, who is sick in quarters, who is on leave, Absent, on special liberty, etc...

The first column is for the person's rate and rank, the second column is for the person's name and the rest of the columns, excluding the very last column are for marking the person's status. The last being for listing his qualification level.

The way I am tracking the info is by using the sum formula. I put a (1) in the appropriate block and the info is summed in a box at the bottom of the sheet. Then all of that info gets summed with the matching blacks on all of the other sheets and is placed on the very first page. In other words the first page is for the whole command and each sheet behind it is for the different departments. All of the info from the different departments ends up on the first page for quick look at how many people are present in the command, how many are at sea, how many are Sick in Quarters, etc..

I hope I have explained it well enough but I fear my limited knowledge of Excel will hinder me getting my point across.

Ok, here is what I am wanting help with.

#1 I want to set it up so that nobody can change the set-up except for the administrator. I only want them to be able to change info in the individual cells.

#2 I would like to replace the ones with something functionally easier. Right now if someone was on leave and are now present, you have to go in and type a one in the "present" box to the right of his name and then go to the "On Leave" box and delete the one that is there.

How would I make it so that the department Mustering Petty Officer simply has to click on the "Present" box and a (1) or another marker would appear in that box and the one in the "On Leave" box would automatically disappear?

If I were to use a simple Dot or other marker, I would first need to know how to selct that marker to be used and then how would I make them add up since they are not numbers.

Using the (1) is ok but I think a dot or some other marker might look more professional.

#3 I have a set of cells in the bottom right that are used for the Mustering Petty Officer to enter his name and then the date. If he is the same person that performed the muster the day before all he will have to do is double click the date and adjust the day or day and month number and then click out of the cell.

The problem is that I know some will simply forget to adjust the date. That is a problem because I have that date set to transfer to the first page with the rest of the information transfering from the sheet to the Main sheet. This allows the Command Mustering Petty Officer to simply scan the date column to verify that all of the departments updated their muster sheets.

What I would like is for that date to adjust to the current date automatically and i thought the best way for that to happen is for the cell that the Mustering Petty officer uses to enter his name into, be the trigger to tell the date to update. then have some auto feature that makes those names automatically disappear from that block once entered and maybe just be present in a Block on the main command page beside the date.

I tried to lok in the tips but trying to figure out where to even begin looking was like trying to read Greek.

I know this was long so i thank anyone who read the whole thing and I appreciate any and all tips I can get to make this work.

#1

Depending on what version of Excel will depend on how well sheet and/or workbook protection will work. Go to Tools->Protection or check Help on 'Protection'

#2

Insert a third column titled 'Status' for example. In the first cell under 'Status' go to Data->Validation and select 'List' from the Allow box and make the Source the remaining title headers (Sick, Present, On Leave, etc). Copy this down for each person. These cells should produce a drop down menu to select each status that a person could have.

In the first input cell in column 4 tyor in this formula (assuming you start input at cell D2) =IF($C2=D$1,"#",""). Copy this cell for all your input area. Your total formula wil now be =COUNTIF(D2:D20,"#") for the first column of input and this can be copied across to the last (change D20 to be the cell just above the total cell).

Now, all that a person needs to do is change the status column and the #'s will adjust accordingly. I hope I have explained this well enough.

Cheers!

This may help with Q 3

put this code in the ThisWorkbook VBA component.

This needs a cell named "TheDate" or change Range("TheDate") to Worksheet("Main").Range("A2") for example if the date is on a sheet called "Main" at cell A2.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim RecoDate As Date

If Range("TheDate").Value <> Date Then
Update = MsgBox("Do you wish to update the date?", vbYesNoCancel, "Update Date")
If Update = 6 Then
Range("TheDate").Value = Date
ElseIf Update = 2 Then
Cancel = True
End If
End If

End Sub