Collect and consolidate data

We have Office 2010 including Sharepoint 2010 deployed in our organization. Every month we collect a list of fixed assets from our subsidiaries using unstructured Excel worksheets. A user must go into each of these worksheets to run calculations on the data and consolidate the information. I would like to add structure to this process without making it more difficult for our subsidiaries. I thought about changing the worksheets to be more structured and then read out the data with programming, but using Office 2010 tools, what is the best solution to this problem? This is obviously a very common use case, but I've been out of the loop for five years doing Java web development. I'm assuming I should not have to do custom web development to streamline this process. One constraint is that we might not have Access Services or Form Services activated in our environment.
grog53444Asked:
Who is Participating?
 
broro183Commented:
hi grog53444,

This is only one possible approach out of many...

If you can set up a standard excel template, you will find it much easier to consolidate the information. I recommend you follow your suggestion & change the worksheets to be more structured and then you may find the below addin to be very useful for consolidating from multiple files.

The JKP Name Manager, is a very useful and free excel addin, collaboratively created by Jan Karel Pieterse (JKP), Charles Williams, (www.decisionmodels.com) and Matthew Henson (mhenson@mac.com) and can be downloaded from: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp?AllComments=True

I use it in my work almost every day and it is regularly updated as bugs or optimisations are identified so I occasionally compare the latest Build number on the website (currently it is #630) with the version on my machine.

Even if the use of excel is only an initial step, or "phase 1" in streamlining your processing, the above should still  be useful. You could develop your process further to use "SharePoint lists" but then again... these may not be very suitable for the particular datasets you are collating.

If you are interested in some good (well, I think are!) spreadsheet design practices I recommend you have a read of: www.eusprig.org/smbp.pdf which can be sourced from http://www.eusprig.org/best-practice.htm 

hth
Rob
0
 
grog53444Author Commented:
I will start by standardizing the template and consider the addin mentioned. The general spreadsheet design practice links were excellent.
0
 
broro183Commented:
Great, I'm pleased I could help :-)

I'm sorry, it looks like I put the wrong addin paragraph/link into my last post (I have some standard suggestions that I occasionally copy & paste). You may find this addin of Ron DeBruin's more appropriate (than the JKP Name Manager) as it provides "a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook": http://www.rondebruin.nl/merge.htm

hth
Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.