I'm having a lot of trouble getting this formula to work:

Please Login or Register  to view this content.
I wish I could make this post shorter, but I'm not sure what details will be critical to getting an answer, so I've included all details.

The way it works now is I have a master workbook named “2011 EP” (2011 Engagement Plan). This workbook takes my input on a monthly basis, combines it with metrics provided by my home office (each monthly metric is named 01.xlsx, 02.xlsx…), and shows a snapshot of our effectiveness compared to our activity. I am a trainer, and the workbook shows my effectiveness as a trainer for my individual office’s employees.

At the end of every month we are expected to update our employee list and our activity with each of them. Then the workbook looks up the contact from my sheet in the monthly metric report and returns a value for each category we train in. The monthly metric report contains the data for every employee in our company (around 12,000 people in total), yet my individual office has 120-150 employees depending on the month (each office in my organization is designated by a number).

As it is right now, they have us manually updating our employee list and we have a high turnover, so this can be a burdensome process. We can’t just delete or copy/paste people in because there are formulas built into the worksheet that end up not functioning when you do that, and they will reprimand me if I have any errors in my report.

What I want to do is be able to select the office number I'm running the report for and then have the sheet autofill each employee. I have been able to make this work by copying the text from the workbook provided by home office into my workbook and then calculating from there, but after simulating this over a 12 month period my workbook becomes too large to send to home office.

I know the index function could get the job done, but I am having trouble with syntax. There are a couple factors that I think the error may be arising from. The first is our office numbers are designated by 1-128. However, in the monthly metric report the numbers are provided as 5 digit numbers that make use of leading zeros (ie-office #48 looks like “00048”). In other formulas I’ve had to use the text function to avoid errors with this and the formula ends up looking like text(b2,”00000”).

The other issue I’m coming up against seems to have to do with referencing cells in a separate workbook. So, here’s the formula I’ve written. Below it is another variant that I’ve tried without success. Does anyone see the issue that’s causing this to not work? I’d love to share the workbook and monthly metrics, but it’s confidential information. The formula is to go in EP 2011. I have it looking up info from a four column section from 01.xlsx. Column 1 contains the office number, column 4 contains the employee name, the range lookup is A2:D12500. Also, B2 contains the office number. 
Please Login or Register  to view this content.
Please Login or Register  to view this content.
Should I use a different formula altogether? Any guidance will be greatly appreciated