Complying a master data spreadsheet with data from mutiple workbooks

[hes1985]

Hi

I want to be able to comply a master data of all my statistics of my various employees. At present each employee has their own workbook where they input their data on a daily basis and it calculates the monthly total.
E.g Mrs. A's workbook worksheet JAN18

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64" span="7"> </colgroup><tbody>
[TD="class: xl65, width: 75"]

[/TD]
[TD="class: xl65, width: 64"]total pts[/TD]
[TD="class: xl65, width: 64"]np[/TD]
[TD="class: xl65, width: 64"]np seen[/TD]
[TD="class: xl65, width: 64"]returns[/TD]
[TD="class: xl65, width: 64"]returns seen[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]units[/TD]

[TD="class: xl66, align: right"]01/01/2018[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]80[/TD]

[TD="class: xl66, align: right"]02/01/2018[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]95[/TD]

[TD="class: xl66, align: right"]03/01/2018[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]90[/TD]

[TD="class: xl66, align: right"]04/01/2018[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]95
[/TD]

[TD="class: xl66, align: right"]05/01/2018


[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]
[/TD]

</tbody>

I then want my own workbook which complies all this data in the one workbook.

E.g
[TABLE="width: 399"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]total stats[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total pts[/TD]
[TD]np seen[/TD]
[TD]returns seen[/TD]
[TD]units[/TD]
[/TR]
[TR]
[TD]Mrs A[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]360[/TD]
[/TR]
[TR]
[TD]Mrs B [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There would be a different worksheet for each month for each employee and then a different worksheet for each month for the total statistics.

I would like to the formula to be able to link the name of the employee to as in column A to the filename of their workbook and then also to the name of the worksheet. For example, the formula reads ='[Mrs A.xlsx]JAN18'!$B$34 for the total pts seen by Mrs A in JAN18. I would like to [Mrs A.xlsx] to reference the cell C1 so that I could use the same formula rather than having to fill in a new formula for each cell. It would also need to reference the workbook's name which would be JAN18 for both workbook (the master and the employee individual worksheet).

Hope I have explained myself correctly

Many thanks

 

[Robby19]

I believe you would be better served by using a split database in Access. Each employee would have a copy of the "front-end data" and be able to input their numbers/data. You would then be able to pull reports based off of certain criteria, such as employee or whatever would suit your purposes. That way you can have all data in 1 spot rather than having numerous spreadsheets out and about.