My company does these projects and we keep track of the progress in excel. From initial receipt of the signed quote, to when we book the project as a future sale in our system. We used to have 1 person doing this for 3 regions and everything was fine. We were confident enough in her excel abilities and if there were issues with the data, there was one person to go to. Now, we have 1 person for EACH region and their abilities in excel are...."needs improvement". Ultimately each of these individuals (project coordinators) send out a report to the sales director of that region which shows the sales person and the amount they have sold month to date, and how much sales they have in each of the 6 different stages of processing a sale, then it will also tell you the name of the project. The report that is sent out is just a pivot table summary of the data I just mentioned however the data source actually has A LOT more data in it (ie, margins on the sale, dates when each sales stage is completed so we can calculate how long it is taking to move a project through the system, etc). But having 3 people do this causes problems. I had to create a shared file with a tab for each region. And the project coordinators would maintain the data in their own tab so they don't trip over each other. The problem with a shared file, is that you can't use a pivot table. So I had to create a separate file that is sort of the 'Master Data'. So at the end of the day, the project coordinators are to transfer all of their data from their tab in the shared file over to the master data, refresh the pivot table, and send the report to the sales director.

This has started to cause lots of problems, from missing data to inaccurate data and all sorts of things in between. I'm hoping there is another way for the PCs to enter their data (a form maybe?) then have that data exported to a sheet that I run a report off of. Maybe Excel isn't the best tool...maybe Access is better. The PCs would need to be able to update data that they have already submitted. For example if project ABC's total value were to change from 100K to 125K, that would need to be easily updated. Any thoughts on this are greatly appreciated.