 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007Pulling in data from another source
Hopefully someone can help with this one. Each week our department generates pivot reports and the naming convention includes the date the report is generated. For example, REPORT A will have 52 reports by the end of the year - one for each week in the year. In the reports (above the pivot component), data from another source is pulled in ... stuff that can't be included in the pivot source... such as headering info, dates, comments etc. I will this source "Extra A". An example of pulling in info is like this: ='M:\'Extra A'!$C$6 The data in "Extra A" changes each week and is only specific to that week's reporting. The issue that was just recently discovered is totally throwing me for a loop and I don't know how to get around it. Each time anyone opens a report the links pop-up window appears (to update the links or not). If I open up a report from 2 weeks ago, I don't want to update the links (because the report needs to remain static and NOT be refreshed with this weeks info), plus I also opened up a report from 4 weeks ago and DIDN'T update the links (I wanted to compare the reports)... and lo and behold the report from 2 weeks changed to be the same as the report from 4 weeks ago. I didn't have the source open, but somehow the report I opened up last, changed the data in the earlier report??? I tried this out numerous times with the same results. How can this happen? And how can I get around it? The older reports are for historical purposes - but they change if mulitple reports are open. this is a fairly common problem. If you want a full copy of the pivot table to remain static, you can copy the pivot table and paste it in a new sheet, first as regular paste (to get format)and then as values to hard copy data. Let this be your historical file for that date. and just let the pivot table reflect current data only this will also reduce the size of the file > Hopefully someone can help with this one. > [quoted text clipped - 30 lines] > The older reports are for historical purposes - but they change if mulitple > reports are open. Unfortunately, we have ALOT of reports (approx 50) that are produced each week... and to copy them would be horrendous. Any other ideas? Do you know why the links even get updated? I thought they would only get updated when either a) the links were updated on purpose or b) the master source and the reports were open at the same time (which seems to be automatic whether you want this to happen or not). > this is a fairly common problem. > If you want a full copy of the pivot table to remain static, you can copy [quoted text clipped - 38 lines] > > The older reports are for historical purposes - but they change if mulitple > > reports are open. With that number of reports, you would be best off to use the Macro recorder to record the steps taken to copy & paste the pivot table, then turn the recorded steps into a generic, re-usable macro > Unfortunately, we have ALOT of reports (approx 50) that are produced each > week... and to copy them would be horrendous. [quoted text clipped - 48 lines] > > > The older reports are for historical purposes - but they change if mulitple > > > reports are open. The other thought would be to save all your data - week after week - in a database. SQL Server 2005 Express is a free and powerful database perfect for such an effort. Then create your Pivot table reports on the fly using queries to extract the data you need for whatever time period is appropriate. > Unfortunately, we have ALOT of reports (approx 50) that are produced each > week... and to copy them would be horrendous. [quoted text clipped - 48 lines] > > > The older reports are for historical purposes - but they change if mulitple > > > reports are open. Hi, thanks for the ideas.... however I can't use the macro to copy/paste reports as the managers need the drill down capability of the pivot reports. I'll have to research your other idea.. but not sure if that will get me around the "links" problem (which is totally separate from the data used for pivot reporting). Thanks > The other thought would be to save all your data - week after week - in a > database. SQL Server 2005 Express is a free and powerful database perfect for [quoted text clipped - 54 lines] > > > > The older reports are for historical purposes - but they change if mulitple > > > > reports are open. The database concept is especially applicable here. Not only do you keep the data underlying the pivot table in the database, you can also put the additional information into a different database table, keyed on week and report type. When somebody wants to run Report A for 6 weeks ago, the database queries pull in both the pivot table data AND the header info and other related data. Far easier than maintaining weekly versions of 50 different reports. > Hi, thanks for the ideas.... however I can't use the macro to copy/paste > reports as the managers need the drill down capability of the pivot reports. [quoted text clipped - 63 lines] > > > > > The older reports are for historical purposes - but they change if mulitple > > > > > reports are open. Ok, interesting. I'll look into it. Thanks for your help. > The database concept is especially applicable here. Not only do you keep the > data underlying the pivot table in the database, you can also put the [quoted text clipped - 72 lines] > > > > > > The older reports are for historical purposes - but they change if mulitple > > > > > > reports are open. some equations will update when the other file is not open. other equations will up date only whan the file is open uyou can play with the <edit><links> section to say which lnks you want to automatically update. I have not played with this one enough to give any hints, but you might be able to set up a macro to not update any links and another ot update links when you want to. > Unfortunately, we have ALOT of reports (approx 50) that are produced each > week... and to copy them would be horrendous. [quoted text clipped - 48 lines] > > > The older reports are for historical purposes - but they change if mulitple > > > reports are open. I didn't realize you could control the links. I tried to Edit the links, but for some reason I can't select manual (it's greyed out)? The update is set to "automatic". How can I get this to change? Thanks, > some equations will update when the other file is not open. other equations > will up date only whan the file is open [quoted text clipped - 57 lines] > > > > The older reports are for historical purposes - but they change if mulitple > > > > reports are open. I get the feeling you have a shared document and I think you need to unshare the document before you can use the links control. From looking at help I think if you reshare, what you set will stay. I am not real familiar with link controls so do not know if this will work > I didn't realize you could control the links. > [quoted text clipped - 65 lines] > > > > > The older reports are for historical purposes - but they change if mulitple > > > > > reports are open. Nope, not sharing.. but I appreciate you trying. I looked up some info on websites regarding my situation have discovered this is a known issue ...(where multiple workbooks are open and settings get automatically overridden). Even VB coding can't get around it. I will try researching Duke's suggestion as well. Thanks for your help. > I get the feeling you have a shared document and I think you need to unshare > the document before you can use the links control. From looking at help I [quoted text clipped - 70 lines] > > > > > > The older reports are for historical purposes - but they change if mulitple > > > > > > reports are open. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.