Collect and consolidate data

Posted on 2011-10-08
Last Modified: 2013-11-05
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.
Question by:grog53444
    LVL 10

    Accepted Solution

    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, ( and Matthew Henson ( and can be downloaded from:

    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: which can be sourced from  

    LVL 44

    Expert Comment


    Author Closing Comment

    I will start by standardizing the template and consider the addin mentioned. The general spreadsheet design practice links were excellent.
    LVL 10

    Expert Comment

    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":


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Title # Comments Views Activity
    copying from excel to word 2 28
    Vlookup for IP 3 33
    Error while setting up the Server Farm Account 8 22
    Excel Averageifs 2 15
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now