I have 3 tabs, PR, Data, PR & Data. I export data from a time keeping system onsite and drop it on the Data tab. The PR tab has employee payroll rates. The PR & Data tab is attempting to pull from both for Name, Dept, Project, Day Worked, Year, Month, # of Hours worked, Hourly rate. I figured out how to pull info for Name and Dept through an INDEX and MATCH formula but I am struggling on the Project, Day and Hours because those per the data drop can have different values. I need to have close to 40,000 rows in the formula. Columns for my tabs, {PR Tab A1-H1: Name, Dept, ID, Effective Pay Date, Rate Type, FT/PT, Annual Salary, Hourly Rate} {Data Tab A1-J1: NA value, NA value, Name, Dept, Project ID, Project, Day, Yr, Mo, Hours} {PR & Data tab A1-H1: Name, Dept, Project ID, Project, Day, Yr, Mo, Hourly Rate}
-
It would help to have a sample of the data and the expected result– cybernetic.nomadMar 23, 2018 at 13:09
-
Absolutely. Can you tell me how to get a screen shot posted here so you can see?– PatrickMar 23, 2018 at 18:36
-
Post a link to the image, or better yet, edit your question by adding some data in a table– cybernetic.nomadMar 23, 2018 at 18:38
Add a comment
|
1 Answer
PR & Data looks same as Data with columns A and B removed, I assume the "formulas" for that are no problem as it is simply referencing cells, with no calculations involved. For the cost per hour, your should be able to use the following formula on row x:
=VLOOKUP(Ax,<PR Tab table>, 8) * Hx
(Ax being the cell with the name on row x, being the entire table in PR tab and Hx being the cell with the number of hours
Not sure what you're using INDEX and MATCH for...
answered Mar 27, 2018 at 0:08
