Pulling data from moving cell

The file Daily_Call_Center_Sales_by_Agent11-8.xls is created by running a report from a web server.

the file Agent Sales Average.xls is a sheet that I update manually to keep a running weekly average of each agent's sales.

I'm attempting to find a way to automate this process.

the issue is that each agent's sales are listed in rows, so the weekly average moves in relation to their username.

Can anyone point me in the right direction of a macro that I can run in Agent Sales Average.xls that matches the username in column B with the username in columns d through i. on Daily_Call_Center_Sales_by_Agent11-8.xls and adds the amount to the last column in Agent Sales Average.xls?

I'd do this in two steps. First, normalize the report data into a table :
1) create a new sheet in Daily_Call_Center...
2) Create a name range in the new sheet in cell a1. "Data_Out"
3) Run the following:

Please Login or Register  to view this content.

Once you have this output table, you can use a simple vlookup to update the Agent Sales Averages.

Hope this helps

SAE

You rock! Thank you!