How to improve spreadsheet tool

[pepper1]

Hello everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I would like to hear some ideas from the experts concerning my current challenge:<o></o>
<o> </o>
The department is using a pretty complex and large spreadsheet as a reporting tool. Lots of data is being put into the spreadsheet (by copy-and-paste) from four different systems which build the foundation for all calculations.<o></o>
<o> </o>
The calculations by themselves are pretty easy and nothing special (simple percentages, calculate proportions of totals and so on). Still, due to the fact that all that data is being pulled from all these systems, they need to be prepped and harmonised. A summary sheet contains all calculated figures and some of the wildest Excel formulas I have ever seen in my life. Unfortunately, it takes forever to calculate the spreadsheet due to the high amount of data and complex formulas. Additionally, it is not easy to perform any big changes on this spreadsheet.<o></o>
<o> </o>
I would like to improve this spreadsheet to be more user-friendly, somewhat automated when pulling data from different systems, still perform calculation formulas and still having the ability to provide good reporting sheet for management.<o></o>
<o> </o>
I have been research quite a bit and found a couple of interesting ideas:<o></o>
<o> </o>

• Database<o></o>
  • Create a database and connect it with Excel.<o></o>
  • My concern is if this will actually increase performance because it is still a large amount of information which needs to be calculated. If I connect it with Excel, the data is inserted into the spreadsheet!
• Open source Palo Suite<o></o>
  • I am not quite sure if this is the right tool, but it looks very interesting. But how do I perform calculations in the cube??<o></o>
• Other reporting tools such as Jasperreports<o></o>
  • Could this be the solution?<o></o>

<o> </o>
So, I would like to hear your thoughts on this. Any help is greatly appreciated! <o></o>
<o> </o>
<o></o>
Thanks,<o></o>
Pepper1

 

[ISY]

Hallo

Before you have already assessed the following link?
http://www.powerpivot.com/

 

[Akashwani]

Hi Pepper1,

Welcome to MrExcel.

ISY is right, Power Pivot is the way to go IF you have Excel2010.

Take a look here, MrExcel explains what Power Pivot is all about....

http://www.youtube.com/user/bjele123#p/search/22/ms149Qi2PVA

Good luck with your project.

Ak

 

[pepper1]

Thank you very much for your replies


The PowerPivot Add-In looks very interesting! Unfortunately, I don't have Excel2010 but I will try it with an evaluation copy.

• Quick question: Can I connect PowerPivot with SAP? How about JD Edwards or Cognos Powerplay

If I used an alternative route by creating a database:

Would I see big changes in performance and speed if I created a database with all information (which is currently stored on worksheets) and would connect it with Excel?

Many thanks,
pepper1

 

[Akashwani]

Hi Pepper1,

Sorry, I can't answer your questions.
Probably the best thing to do is search the internet.

Sorry that I couldn't be of more help.

Ak