Excel Spreadsheets

5 pts.
Data analysis
Microsoft Windows
SQL Server
We developed some kind of database with excel spreadsheets to draw monthly stats for diffrent departments. It works as follows: Individual spreadsheets(formulated)feeds into a master Link spreadsheets(data) we then use Pivots for our reports. The problem that I now expierence is that: 1. The masterlink does not update automatically, we have to open the individual spreadsheets and only then does it update. 2. The pivots update but if you change any information on the individual spreadsheets it does not reflect the changes on the pivot,however the changes reflect on the masterlink data. I tried to copy the masterlink data on a diffrent sheet,and reconstructed the pivot It worked the pivot updated data(problem was the pivot) What could be the problem? or do we need to look at a diffrent ways of getting the stats we need. Any suggestions.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try checking to see if the properties for auto refreshing the data when you open the workbook are set. This can be set to automatically open the required spreadsheet(s)/data file without user intervention. I use this to get data from the web automatically when I launch a workbook for epos data to be collated.

I am not exactly sure why the 2nd problem occurs, but if you change data on the source you might have to select the manual refresh from the toolbar to get it to update. This could be because the source is only checked when opening the workbook with the pivot on. Have you tried to set the automatic refresh frequency period, or is it set to only get data on opening.


Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Nansimon
    I would check the link information very carefully. Excel has a tendency to store the linked file location using the drive letter rather than a UNC or relative path, and if it's not exactly the same on every PC it doesn't work right. I would also consider using a different storage format for your data. Excel is not designed to work like this and every time I've seen it used like this it causes problems and headaches. If the data is small enough and you only have a few people accessing the information, try putting it into MS Access. If it's thousands of data points, the data changes several times a day, or you have more than 10 people or so using it, it's worth investigating a serious database (take your pick from what you already have expertise in). Excel can use MSQuery (which comes with Office) to get the data to feed into the pivot tables, and the refresh options should allow for periodic updates. -Nancy
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: