Department report in Excel large file size - Separate sheets into multiple files, keep link so that master file updates

[jurgminator]

Hi guys,

I've been pondering this for a little while, figured it would be better to ask the experts.

I have a workbook that functions as my department's reporting platform, containing 4 sheets:
- Data entry
- Print layout
- Resources
- Imported data

I've used a lot of filter and lookup functions to populate the print layout, and VBA to hide and fit rows based on results.
The file has grown very large as a result, and is around 18MB.

I started thinking that maybe copying the data entry sheet out of the master workbook could be a solution.
The master workbook would need to be updated when there are new entries in the data entry workbook.

The report will be stored in sharepoint, and linked in Teams.

Is there any way to accomplish this without editing all references manually?



Bonus question (I might create a new thread for this at a later point):
The data entry table grows substantially every week as new incidents are reported.

Is there any way to always have a few empty rows at the top of the table so that they dont have to scroll to bottom to add new entries?

I've looked into Forms, but that doesnt seem to be a good solution when adding multiple entries in one go.