Help setting up a functional sheet

Hi,

would be good to see what you have so far.

Otherwise, create a table with a column for each of these fields: Date, Employee#, Name, Reg hrs, .... etc, so basically all the fields.

Then create a row entry per employee for each day to log the hours.

You can then use a pivot table to summarize the data, create totals, different categories, etc. Use different pivot tables for different aspects.

hth

Hi,

try the attached. I've left your test1 and test2 sheets alone. By the way, the layout of test2 is not good, because you'll soon run into the Excel 2003 column limit of 256 per sheet, and it is quite complicated to summarize by date.

Anyway, have a look at Sheet1. I've added a column for Date at the left, and a column for "filter month" at the right. I'll explain below.

I have used the Excel 2003 List feature for the table (Data - List - Create list). You see a blue border around the table. When you click somewhere in the table, a new row for data entry is opened below the last table entry.

The List also has a Total row. Click in any field on the total row and select from the drop down what kind of total you'd like to display. I've used sum for the columns reg, OT, Hol, Prem and Other.

Data entry happens thus: click anywhere in the list to open a new row for data entry. Then enter the date, and all the other info. I've put data validation on the employee number, so any number that is not in the employee list on the "lookups" sheet will be rejected. The employee name will be filled in with a VLookup based on the employee number. This should minimise typos.

If you add / delete names on the Lookups sheet, the range name that defines the lookup will automatically adjust (go Insert - Name - Define and see how the range names Employee_number and Employee_table are set up).

So, for each row you need to specify date, Type, employee number, boat, and then enter how many hours for reg, OT, Hol, etc. I was not sure if Prem and "Other" is hours or dollars, but I'm sure you'll work out what you need.

If an employee spends half a day on boat 1 and the other half on boat 2, you create two entries for that employee for that date and select the boat in the respective colum. I've put a few examples in there (e.g. employee "sample 1" in october)

You seem to be familiar with the autofilter, since your spreadsheet had it enabled. The list automatically has autofilter turned on, so you can use this to create summaries and reports.

For example select an employee and then a month from the rightmost column and see the list filtered.
For a different date range, you'd have to use the Custom option in the date column and then specify "Date is greater than" AND "Date is less than" ...

The beauty of the list is that the total row below the list only totals the visible rows. So you can quickly define a few filters and see the summary.

I've duplicated the summary values above the list, so you don't have to scroll down all the way to see the totals.

If you want to change the structure of the list, for example add a column, it is best to first go "Data - List - convert to Range", then add columns, formulas, etc. and finally go "Data - List - Create List" again

This is only a starting point and you may have lots of questions. But basically with a layout like this, you can track all kinds of information. The list filter is an easy way to create some basic reporting. If you need more complex stuff done, you may need to explore pivot tables after all. A good starting point to learn about pivot tables (other than the Excel Help) is www.contextures.com

Let me know how you get on with this.

cheers