Match data and move from one workbook to another

[dmaner]

I need some macro help.

I'm attaching 2 files so that my issue can be better understood.

What I'm trying to do with a macro is:
1. find the first employee in the list (employee's names will not always be the same) from Worksheet1.xlsx
2. find the first metric under this employee's name
3. copy the Total Time (Hours) from column E from Worksheet1.xlsx
4. find the metric and the employee's name in Worksheet2.xlsx and paste the data in the corresponding cell
5. find the second metric under this employee's name and then repeat steps 2 through 4 until all metrics are fulfilled for first employee
6. find the second employee in the list from Worksheet1.xlsx and repeat steps 2 through 5
7. repeat steps 2 through 5 until all employees in the list from Worksheet1.xlsx are complete

Note that the metrics under each employee may vary on Worksheet1.xlsx but the metrics on Worksheet2.xlsx will always be the same.

If anyone can help me with this, I would be tremendously grateful! Thanks!!

[Sisyphus]

Hello deidre,
This formula can be pasted into each cell in your Activity Sheet:

Code: Select all

=IF(ISNA(VLOOKUP(C$3,INDIRECT("Sheet1!A" & MATCH($B4,Sheet1!$A$1:$A$490,0) & ":E" & MATCH($B4,Sheet1!$A$1:$A$490,0)+MATCH("Total:",INDIRECT("Sheet1!B" & MATCH($B4,Sheet1!$A$1:$A$490,0) & ":B490"),0)),5, FALSE)),0,VLOOKUP(C$3,INDIRECT("Sheet1!A" & MATCH($B4,Sheet1!$A$1:$A$490,0) & ":E" & MATCH($B4,Sheet1!$A$1:$A$490,0)+MATCH("Total:",INDIRECT("Sheet1!B" & MATCH($B4,Sheet1!$A$1:$A$490,0) & ":B490"),0)),5, FALSE))

It looks a little unwieldy but it will look up both, the name in column B on the left and the activity in row 3 on top. If the activity isn't listed under the name zero will be returned, else the total from column E on Sheet1.

You need a slightly lighter formula in the Total column on the Activity sheet:

Code: Select all

=INDIRECT("Sheet1!E" &  MATCH($B4,Sheet1!$A$1:$A$490,0) + MATCH("Total:",INDIRECT("Sheet1!B" & MATCH($B4,Sheet1!$A$1:$A$490,0)+1 & ":B490"),0))


Do I get a discount now on my next virus protection suite?

[dmaner]

Haha, I wish I could give you a discount

Thank you so much for this! I have been wasting time trying to figure out code when formulas seem to do the trick.