Creating a new workbook that has this feature....

  • Thread starter Thread starter GateGod
  • Start date Start date
G

GateGod

Hi all,

My boss wants a spread sheet created that will tally all the factors
into one area.

For example:

Accidents
Injuries
Accidents with injuries
Reportable accidents
Total damages


Just to name a few things. Now what I want to do is have it set up
like an Attendance Log sheet we currently use. Problem is I can't find the
formula it uses for some parts. Again Example it has on it:

Absent
Vacation
Sick Leave
Bereavement
Floating Holiday
Personal Day

What I can't figure out, (with or without Excel help files), is when you
place in the left column the hours used, then place a code like "SL" for
sickleave, it automatically tracks and deducts it from the master total. How
or what formula are they using that it knows what the SL, V, FH, PD, B, to
name a few, are and what to do with them. Any info is and will be greatly
appreciated. Thanks in advance!!

Dale :O)
 
hi Dale,

Let me get this right. In the log sheet, employees (or someone else) would
enter:

EMPLOYEE ENTRIES
Column A Column B
SL 8
V 2
FH 8
SL 8

and you would like a summary that says:
SUMMARY
Column A Column B
Sick Leave: 16
Vacation: 2
Floating Holiday: 8

Assuming that I'm right, then you could use a VLOOKUP or a SUMIF function.
With the VLOOKUP, you'd have a listing somewhere that looks like this:

VLOOKUP DATA
Column A Column B
Sick Leave SL
Vacation V
Floating Holiday FH

then in your summary, you'd have:
Sick Leave: =SUMIF(A1:B4,VLOOKUP(D7,$D$1:$E$3,2,FALSE),B1:B4)
Vacation: =SUMIF(A1:B4,VLOOKUP(D8,$D$1:$E$3,2,FALSE),B1:B4)
Floating Holiday: =SUMIF(A1:B4,VLOOKUP(D9,$D$1:$E$3,2,FALSE),B1:B4)

where:
- A1:B4 is the "EMPLOYEE ENTRIES" data (change this cell range as
appropriate)
- D7 is the cell that holds the words "Sick Leave" in the "SUMMARY" area.
D8 holds "vacation" and D9 holds "Floating holiday" so on and so forth.
Change these cell references as appropriate.
- $D$1:$E$3 is the "VLOOKUP DATA" data (change this cell range as
appropriate)
- B1:B4 is the hours column (Column B in this example) in the "EMPLOYEE
ENTRIES" data (change this cell range as appropriate)

If you didn't want to use the VLOOKUP, you could manually add the criteria
into the SUMIF function. eg =SUMIF(A1:B4,"SL",B1:B4) for the Sick Leave
category and so on and so forth for the others.

Does this help get you started??

Katherine
 
Back
Top