Reorganizing Raw Data into a Report

[jrothstein]

I would appreciate any help to solve this....

My company has the worst sales reporting system known to mankind, short of an abacus. Such is life in a small company that doesnt want to spend money. Hence, I am tasked with finding a way to view reports outside of the arcane reporting system that is currently available.

I have gotten as far as I can with this, and is reflected in the attached Excel file. It contains three worksheets: Raw Data, Sales Report, and a departmental employee list. Our current sales report contains the data that is in the raw data worksheet. In this spreadsheet, data is to be "dropped in" every day, via copy/paste, from the current system. I have deleted several colums for simplicities sake. Hence, the data as you are viewing it is accurate and in the correct columns as would appear when data is dropped in every day. There will be nothing below the sales data as shown.

So in other words, every day the manager wants to open our current system, copy the sales data, open this Excel file, and paste the data into the Raw Data worksheet and view the resulting report.

The Sales Report workbook contains the work that I have done already, although I have deleted some of it for purposes of clarity. It uses Index/match to find the correct values from Raw Data workbook for the respective employee. It actually works!

HOWEVER, it is incomplete for a very important reason, and here is why:

The raw data will contain sales data from employees that are not to be reported on the sales report. The sales report needs to present data for only one department, not the entirety of the raw data. HENCE the third worksheet: Departmental Employee List.

So what I need this to do is this: after data is dropped into the Raw Data worksheet, a Sales Report is automatically generated based on just the employees that are on the Employee List. If they are not on the employee list, they are ignored.

Additionally, here are a few business and logistical rules that needs to be addressed: (VERY IMPORTANT)
* CANNOT use macros or VBA. This will be viewed on a Mac using Excel 2008
* The employee list will change as employees are added or deleted.
* There may be days where there is an employee with no sales data in the Raw Data. However, that must still be reported as a 0 on the sales report.
* There are more employess reported in the raw data than should appear in the Sales Report. In other words, the data for these employees will not be used in the Sales Report. They are to be ignored for reporting purposes

Anyone want to take a stab at this? I am sure there is a proper way to do the lookups between the three worksheets to yield the proper report. Any help is appreciated.



Example Sales Report.xls