Pulling Data from Multiple Tabs onto Summary Tab

Hello –

I have three reports that generate monthly. First report has the employee’s pay and estimate amount, the second report shows has a list of 42 different access each employee could possibly have indicated by either a Y or N, and the third report is the monthly employee’s census.

Due to segregation of duties, when an employee is assigned a task, they may need certain access and or payment and estimate authority removed.

What I want to accomplish essentially is being able to take the three reports each month and paste it into it’s own tab in the workbook. The summary tab is where the data is displayed by pulling the information for the three other tabs, pay and est, access level, and census. The census will have who the employee reports to as well.

The summary tab should be broken down first by manager. So when you select the manager, it will show their employees. When selecting an employee’s name, their pay and estimate authority and the types of access they currently have will generate. When selecting the task to give the employee, it will trigger if any access and or pay and estimate authority needs to be removed or added. Now that I think about it, there will be a forth tab, Resource tab, that will display what access and or pay and est authority needs to be removed given the selected task.

I’ve included a sample workbook. I’ve taken out all the data except for the column heading. Anything left on row 2 is pertinent info for the summary tab. Under the reference tab, I’ve made up the task list and the access that has to be removed. I’ve been puzzled with this for about a week and a half now. Any help would be greatly appreciated.

Sincerely,
Tho12

A few questions for clarification purposes:

When you import the monthly reports, do you scrap the previous data and work with the new data only or do the old and new reports have to be compared for changes and some data from the old one kept?

Where do these new reports sit? Are they all in the same folder on a drive? Can they always be found in that folder? Are the file names static? What kind of files are they, .xlx*, .csv, .txt?

Could we see a little more sample data? Desensitized of course. Half of one row of data is not a lot to go on!

BSB