How can I search several excel 2013 Files to compile into a mega sheet?

Question

I am trying to consolidate several Excel worksheets with a master worksheet in order to track progress on several projects.

I am a supervisor and I routinely send my subordinates a master file of 500-1000 tasks to perform, then my subordinates create their own sheets to document their progress on these tasks. Due to the different nature of these tasks the spreadsheets produced by my subordiates look significantly different.

Is there a way to sync their sheets with my master file to easily see their progress on a given task if their spreadsheets aren't formatted the same way?

Answer 1

A simpler alternative to SharePoint is iPushPull. It has an Excel add-in that lets you share ranges of cells between different spreadsheets via a cloud service. It's a simple way to automate manual workflows in Excel.

In the scenario you describe your subordinates would continue to use their existing sheets, but you would configure the ranges of cells that you need to see so that they get automatically pushed up to the iPushPull service every time they change.

You would then configure your master sheet so that it gets updated automatically whenever the source spreadsheets are updated.

There is a free trial so you can try it and see if it works for you.

Disclaimer - I work for iPushPull.