[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Collect and consolidate data

Posted on 2011-10-08
Medium Priority
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
  • 2
LVL 10

Accepted Solution

broro183 earned 1500 total points
ID: 36939358
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 

LVL 46

Expert Comment

ID: 36939910

Author Closing Comment

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

Expert Comment

ID: 36976770
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


Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

872 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