Can some one help?

chunk · May 26th, 2002, 04:43 PM

I’m trying to do a staff absence sheet.
I have several sheets in my workbook, all named in months of the year.
I’m trying to get some totals on a new sheet.
The problem I have is that managers need to input on the corresponding month, hours an employee has off, when they’re either sick, on holiday, or late.
I have it set so that the managers select from a drop down box, S (Sick), H (Holiday), and L (Late).
I want it so that on the totals sheet (in the Sick, Holiday, Late totals), the cell for each total, looks up the number that is above the drop down box that the managers has inputted, for the amount of time the employee has of for that month.
I've done it so that it checks if it’s an (lets say) S and puts it in the totals (on the totals sheet) for S. But not for consecutive days, adding it all up.
Your help it much appreciated
Chunk

[ This Message was edited by: chunk on 2002-05-26 15:45 ]

[ This Message was edited by: chunk on 2002-05-26 15:45 ]

Anne Troy · May 26th, 2002, 05:36 PM

Chunk:

Tell us more. Perhaps you can also offer to email your workbook to someone so that they can help better. Sounds pretty hard to understand, but might not be if seen. Also sounds like you need some VBA, which I don't do, or I'd give you my email. I know that Jay Petrulis did something very similar for me. Except my file was just vacation days.

Good luck.

Brian from Maui · May 26th, 2002, 07:07 PM

Chunk,

Column A houses names of employee
Column B S for sick
Column C hours of sick time

=sumproduct((A1:A100="employee name")*(B1:B100="s"),C1:C100)

You could also have a hidden column of employee names and refer to a "cell" instead of the name.

[ This Message was edited by: Brian from Maui on 2002-05-26 18:11 ]

Hope this works here goes:

Microsoft Excel - Book1

___Running: xl97 : OS = Windows (32-bit) 5.01

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

A1

=

chunk

A

B

C

D

E

1

chunk

s

8

:alert('=SUMPRODUCT((A1:A3=%22chunk%22)*(B1:B3=%22s%22),C1:C3)')>8

2

chunky

l

8

3

chucker

v

8

4

checker

v

8

Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[ This Message was edited by: Brian from Maui on 2002-05-26 18:19 ]

May 26th, 2002, 05:36 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Chunk: Tell us more. Perhaps you can also offer to email your workbook to someone so that they can help better. Sounds pretty hard to understand, but might not be if seen. Also sounds like you need some VBA, which I don't do, or I'd give you my email. I know that Jay Petrulis did something very similar for me. Except my file was just vacation days. Good luck. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 26th, 2002, 04:43 PM	#1
chunk New Member Join Date: May 2002 Posts: 14	I’m trying to do a staff absence sheet. I have several sheets in my workbook, all named in months of the year. I’m trying to get some totals on a new sheet. The problem I have is that managers need to input on the corresponding month, hours an employee has off, when they’re either sick, on holiday, or late. I have it set so that the managers select from a drop down box, S (Sick), H (Holiday), and L (Late). I want it so that on the totals sheet (in the Sick, Holiday, Late totals), the cell for each total, looks up the number that is above the drop down box that the managers has inputted, for the amount of time the employee has of for that month. I've done it so that it checks if it’s an (lets say) S and puts it in the totals (on the totals sheet) for S. But not for consecutive days, adding it all up. Your help it much appreciated Chunk [ This Message was edited by: chunk on 2002-05-26 15:45 ] [ This Message was edited by: chunk on 2002-05-26 15:45 ]