Calculate average $/HR by pulling data from two workbooks

Closed
chouston Posts 1 Registration date Monday March 25, 2013 Status Member Last seen March 25, 2013 - Mar 25, 2013 at 05:41 PM
sgmpatnaik Posts 54 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 - Apr 29, 2013 at 07:47 AM
Hi, I have been researching trying to figure out how to do some data manipulation with VBA, I have been going nuts trying and failing at making this work, if it's something anyone can help with, I would really appreciate it...

The files I am using can be found here:
https://docs.google.com/file/d/0B8A5F4oHm9H3a09FQV93MkZhbFU/edit?usp=sharing

https://docs.google.com/file/d/0B8A5F4oHm9H3MjJJamJFdFlSTG8/edit?usp=sharing

I have two excel files that I am trying to gather information from to calculate the average $/hr earned by employees. The first file, which is currently called "Book 1" is generated by a different VBA code, the other "Employee Hour Output" is generated by our time clock program. What I am trying to do is have the script run from the PERSONAL workbook and look in the Employee Hour Output. In column B there are Employee names, for each employee that worked, there is a list of invoice numbers located in column E. The code needs to see which invoice numbers each employee worked on then compare that to the $/HR earned from that job in column D of the Book1 workbook. Then based on all the jobs worked, calculate the average $/HR that employee earned in the given time period. This information needs to be displayed with the employee name and $/HR in the first available row of columns H and I of the Book1 workbook.

1 reply

sgmpatnaik Posts 54 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 29, 2013 at 07:47 AM
HI

Please Try the below formula for average calculation for book1

in I4 write the below formula

I4: =AVERAGEIF($E$2:$E$70,$h4,$D$2:$D$70)

and copy across

and for second thing i can't understand can you elaborate me once again

Thanks

Patnaik
0