MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF


Posted by Brian on January 13, 2002 10:18 PM

I trying to create two linked two workbooks, one with employees on vacation and one with a running total of the employee's vacation used. I am using this formula:

=countif('[vacation calendar.xls]jan.'!$a$1:$j$100,"name of employee")*8

However not all employees use 8 hours of vacation time, how can I reference the cell that has the actual hours taken?

Thanks......Brian


Posted by Aladin Akyurek on January 13, 2002 10:50 PM

Brian --

Unsure about what you want. Care to post 5 rows from vacation calendar.xls]jan.'!$a$1:$j$100 along with an expected result?

Aladin

==========

Posted by Brian on January 13, 2002 11:26 PM

Aladin

Vacation Calendar is broken by days along with other info such as work assignments etc.:
jan worksheet jan 1
a1 employee name
b1 hours of vacation
c1 start time
d1 work assignment

Second workbook is a Master list of employees with various info such as ytd overtime worked, ytd total of vacation taken etc. I would like to link the employee name and hours of vacation taken from the calendar to the Master list since the employee may choose to take less than 8 hours of vacation. I would like to add hours from the Vacation workbook.
Master list:
a1 employee name
b1 total vacation time taken
c1 total overtime worked

Brian

Brian


Posted by Brian on January 13, 2002 11:33 PM

Aladin

Aladin,

My expected results:
If I take 8 hours on jan 1st and 4.5 hours on jan 2nd, the total hours of 12.5 should be linked to the Master workbook. My formula only multipies by 8.

Brian

Posted by Aladin Akyurek on January 14, 2002 12:34 AM

SUMIF

Brian --

It's:

It's:


=SUMIF('[vacation calendar.xls]jan.'!$A$1:$A$100,A2,'[vacation calendar.xls]jan.'!$B$1:$B$100)

to be entered in B2 in the target sheet where you want the total of vacation taken.

Aladin

========


Aladin