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

	total pts	np	np seen	returns	returns seen		units
01/01/2018	5	2	2	3	3		80
02/01/2018	6	1	1	5	5		95
03/01/2018	6	0	0	6	6		90
04/01/2018	6	1	1	5	5		95
05/01/2018

<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>
</tbody>

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

E.g

	total stats
	total pts	np seen	returns seen	units
Mrs A	23	4	19	360
Mrs B
Mrs C

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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.