How can I improve the process of collating spreadsheets and consolidating from 28 branches.
Scenario
Developed Spreadsheet template
Submitted (by e-mail)to 28 branches to complete (monthly)
Use same Template format to produce Consolidated Report, by cell reference to branch spreadsheets.
Save each submitted spreadsheet in a file to HDD
Open each spreadsheet to print
Need to save all reports on a rolling 13 month basis)
Problem1
If collated spreadsheets from Branch are not saved by the exact file or folder name- Consolidated Spreadsheet will not pick up the data)
Problem2
Time consuming effort of opening and closing each individual spreadsheet to print
Problem2
More than one user – with Beginners excel skills -does not always save spreadsheets to correct file or file name.
Problem3
can only use same file and workbook name every month as otherwise Links in Consolidated spreadsheet does not work
I would be grateful for any solutions.
