Easy way to input data for new projects

I like Google Docs forms for data entry by those who are less Excel-savvy. Google Docs forms allow anyone with the link to enter the data into a spreadsheet/database that you can then download when you need the data to create the reports. The spreadsheet is editable, so that when values need to be changed they can be, by you or by the others.

Hi mvparker79,

I used to do something perhaps not too dissimilar to what you are doing, albeit the content was different. I basically had four regional spreadsheets, each maintained by four different people, one per region. It was their individual responsibility to keep the regional sheets updated each month. I set the sheets up so they were all identical in look and feel and followed the structure I needed.

I would then maintain a single master sheet that would pull all the updated data from the four regional sheets into my master sheet using a macro and then all the monthly reports would be run from my master sheet, which I could maintain complete control over, so I could update just the sections I wanted/needed to update, thereby avoiding introducing errors into my master sheet where the regions had introduced their own errors. The sheets all sat on the corporate network which made this possible without the need for anyone to email or have multiple versions of any sheets, there was always just one sheet per region and one master sheet. This also negated the need to 'share' worksheets, which has its own drawbacks as you have found and is also prone to all sorts of problems.

My master sheet was set up so pivot tables would auto-update along with all other reports and graphs, which allowed me to maintain a consistent look for all monthly reporting which you will probably struggle to maintain if you have four different people all producing their own versions, however much you try.

What you have to bear in mind is that the data is only ever going to be as good as that entered by your regions, if they don't keep their data up to date and accurate then the data out and hence the data reported will not be up to date or accurate, so some of you problems come down to 'educating' the people you are charging with keeping your regional data up to date. If these people's Excel skills are not the best, then as a company spend some time, even if only a day, with them doing some basic Excel training, covering the things they need to understand to keep their data updated. It is a false economy to just assume people will do that themselves or have the skill set to do so and in general people do not ask if they don't know how to do things because they feel embarrassed to do so.

In my experience the vast majority of people who use Excel on a daily basis in a corporate environment have an extremely limited Excel skill set. I spent a day with each region, covering some basic and fundamental things, such as data entry, navigation, basic formulae, pivot tables etc, really just the basics to allow them to do the things I needed them to do and it really helped them and resulted in much better data input, which in turn really helped me to provide accurate, meaningful reports.

So my advice would be, invest some time in the staff and provide some basic training for them (if not yourself, send them on a basic Excel course) and then structure your master sheet to pull data from the regional sheets in a similar way as I described above so you always have one version of the truth and if data is missing or is full of errors at a regional level, go back to the respective regions and ask them to fill in the blanks and correct the errors, otherwise your reporting is largely meaningless.

Just the approach I would adopt.

You're welcome, I think you kind of have to (politely) dictate how you need things to work and give people the confidence and training (if they are unsure) to 'give you what you need' if that makes sense. I've worked in corporate environments and it staggers me sometimes when I look inside folders to see hundreds of files with zero logic in terms of which is the most recent version, zero naming conventions and so on. I think you have to exercise an element of control so you get back what you need, don't expect or rely on others to do so for you, you will only end up disappointed. Hand hold where you need to and invest time so the people you are relying on to give you the information you need, know a) what is expected and b) the basic ability to use Excel to give you that information.

Good luck...