Software

Ask something
Discussion Locked

Improve Consolidating data in Excel

0 helpful
1 reply

by taarga

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

1 reply

by parier

Ever though of Access. Using access you can have a friendly interface that user can/can not edit old date, depending on database creation, then create desired report, based on specification, query (date). When new data is entered (for new month) reports can calculate what is needed based on query, this can be carried from month to month. Reports can also be easily exported to Excel, Word or directly to email address or an email group. Please reply if interested or require additional information to eparisek@mtga.com.

This discussion is closed to new replies.