data source linkage

Hoping someone is an expert with data links.  I store my 
data in access and link it to excel so I can do pivot 
tables/charts.  I've run into two major problems.  a) if 
I change the location of my access file (move it to 
another directory) I loose my link to the pivot table and 
can't figure out how to update it.  Also I create 
multiple pivot tables from the same data source (I select 
everything in my query and just drop in what I need).  
But if I just copy and past the pivot table whenever I 
change my groupings it carries thru to all the copies.  
What's the best way to create multiple independent pivot 
tables based on the same source data without recreating 
the link for each.  

NB: To create my link I choose Data>>Import External Data 
>>Import Data then open the appropirate data source.  
This may be why I'm limited?  Should I use an OLAP cube 
or something?

Any help is appreciated.

Thanks Ian.


0
anonymous (74722)
5/2/2004 7:12:09 PM
excel 39879 articles. 2 followers. Follow

0 Replies
503 Views

Similar Articles

[PageSpeed] 24

Reply:

Similar Artilces:

Data on a 3.5 diskette
I realize this may be somewhat old school, but I have a problem with some data on a 3.5 diskette using WinXP. I put some personal data in a '.xls' file on a 3.5 diskette and update it every now and then. The other day when I put the disk in to enter some new data, I received the following error msg: "book1.xls" cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. My options at this point are "RETRY" or "CANCEL". I check the...

data range is too complex.
I am just changing over to excel 2007. . Most of my data will update to the embedded chart just fine but one tells me "the data range is too complex to be displayed. I don't know how to fix it -- Thanks for all your help. God Bless, Frank I had one like it and all I did to fix it was select the chart and right click on it, select data-> click on the icon at far right of Chart Data Range, use the mouse to select the data range, click on the icon at far right then OK. Note: If there are multiple ranges and the ranges are not adjacent, select the first range then hold the Ct...

Pulling data from individual files to master list
Hello, I've just been entering the world of Excel for the past few months, as I started a new job last year and my main duty is to bring the company into the 21st century (or even the late 20th, at this point). What I'd like to do sounds a little backwards, but I think it's the way to go, if it's possible: I'm creating individual sheets for our products, so that all the relevant info for, say, product A001 is shown on one sheet named "A001.xls". But I would also like to create a "master" list. I say "master" in quotes because it's not r...

Transfer data from one sheet to another
Did I stump you guys? This is a hard one! Here is the senario: Worksheet one has a cell with a drop down menu with the numbers 1-10 in it. Below the drop down menu are two columns labled 'account number' and 'method' each with many fields available for info below them. Worksheet 2 is a database. It has many columns labled 'account number', ' method', 'date', 'cycle' etc. The cycle field is what contains the numbers 1-10 of the previous worksheet. I need a way of making it so that when I choose 1-10 from the dropdown menu on the fir...

export data to excel
Hi im quite new to using visio. im currently doing an office plan and using the "office space" shape to determine the amount of area in a certain office area. What i wanted to know was if there a way to export the office space amount it has calculated (eg 57sq metres) dynamicly to excel so that when i resize the area on the plan the spreadsheet updates. thanks Yes, you can use the "Link to Database" functionality in Visio to export and link the database values. Highlight the shape Click on Tools \ Macros \ Visio Extras \ Link to Database Set the database source to ...

Transfer of Data
I've used the db1 file. Loaded it to the hard drive, clicked it on from where I stored it and Money begins to 'work' then gets hung up. Must have tried 20 times today. Have also tried restoring from the bak ext. No luck. Is it possible to transfer from 1999 to 2003? Sorry for the thread error. I'll make a copy of my file with the .mny extension and see if that works. Is what I'm attempting even possible. ('99 to '03?)? In microsoft.public.money, Lynn Ringuette wrote: >Sorry for the thread error. I'll make a copy of my file >with the .mn...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: http://www.peltiertech.com/Excel/ChartsHowTo/index.html When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

CRM
Hi, I've got a problem to import data into drop down list (CRM). I alreday try cdf tools. But, when I execute the "p_cdf_PopulateStringMap" nothing happens! The cdf_StringMap is not completed... Help! someone has an idea to resolve my problem ? thanks regards, When you have installed the Data Migration Framework, you have also got 2 executables. You can (probably) find them in this folders: c:\program files\microsoft crm data migration tool\cdf c:\program files\microsoft crm data migration tool\cdfmigration one of the tools is called initializecdf.exe. Run this tool and...

Multiple Pivot Tables based on the same data
I have two pivot tables based on the same source. However, each table is unique in how it shows the date. The first shows all thirty days of the month. The second groups the date in 10 day increments. The first table is summed data while the other is averaged data. When I try to set up the second table to group, it also groups the first table even though its' on a different sheet. I remember being able to do this in version 2003. What have I overlooked? Thanks Phil In xl2003, I could create the pivottables as separate entities. I created the first PT and then sta...

averaging data
Hi, I have a program that collects data throught out the day and populates an Access table. This goes on everyday. I collect various data but the important ones to mention here is the date and percent error. I wish to show the average error for each day in a query. Is this the best way and if so how does one do this. Cheers Gerry GC, Create a "Totals" query (in query design mode menu... View/Totals). You would Group on the date value, and probably Sum the value data. This should yield totals for the day... from which you can calculate the percentages of...

Can you create calendars in Excel from data?
I am looking for a way to use the data entered in Excel to make an actual calendar. Is this possible or does Excel not have this functionality? ...

ow to create comparison chart using text as value data?
lplease help me. I am trying to set up a comparison chart of 3 computers cost and features! I cannot figure out how to use the features as values on the x or y axis says data range is to complex so cant select rows or columns. I am trying to enter the features as my source data too bungled dont know what to do term project is now overdue and I am stressing out!~ Dont want to fail. please help me!do i have to redo the whole chart or what any suggestions and demonstration would be deeply appreciated. thanks, Dee Johnson The first thing you need to do is think how the chart should look. Wh...

Import data from a web page
I've read the suggestions about this topic and none of them work. The only thing I am able to do is manually copy and paste data from a website to my Excel worksheet. In the past, with the previous version of Excel I had a web query that worked but now I just get an error message that says the query retrieved no data. Following the instructions in the error message window yields no results. Has anyone else had this problem and arrived at a solution? What version of Excel, previous version is not descriptive of what you had what you have now. And it possibly does not matter. In...

Querying XML data
Hello, Using SQL 2008 I'm taking xml string from a VARCHAR(max) datatype column and inserting into XML datatype column then want to query it. Doing the above like so: DECLARE @XmlSourceTable TABLE (RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, XmlData XML NOT NULL ) INSERT INTO @XmlSourceTable (XmlData) SELECT TOP 1 CONVERT(XML,REPLACE(REPLACE(CSR, '&lt;', '<'), '&gt;', '>'),2) FROM CDATA The xml looks like this after inserting here is just a small part: <SoapGetCSRReturnResponse xmlns="http://temp...

I want to show chart data in order of bars, not row names
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to show a chart that has about 20 values, and I have sorted the source data so that I have the highest value items at the top and the lowest value items at the bottom. For example, if rows were Apple, Banana, Guava, Orange, and Yucca, I have sorted the data in order of the greatest value items (20 Guavas, 10 Oranges, 5 yucca, 2 bananas, and 1 apple). Yet the chart (a bar chart) only seems to let me display in either ascending or descending value by food name (either Apple to Yucca or Yucca to Apple...

how to activate a cell if it contains data
I've created a Pvt Table which contains 6 fixed columns and it will show data depends on how many columns I wanted to see. In addition, I've created another set of columns to copy data shown on the pvt table. So here is my question, is there a way to activate cells with data on the Pvt Table? Here is an example: If I wanted to see 4 columns of data on the Pvt Table: Column A Column B Column C Column D Column E Column F Row1 $27 $30 $49 Total My other Set of columns should appear like this: Colum...

manipulating data from Find dialog in web browser ActiveX control.
Hello everyone, I would like to know how can I get access from MFC to the text that was entered in the Find dialog of the Microsoft web browser ActiveX control. Documentation is pointing to IHTMLOptionsHolder interface but I cannot get reference to it (I am getting E_NOINTARFACE) from any of the available interfaces in my code. Note, I do not have problem to query other standard interfaces exposed by ActiveX control (ex.: IHTMLDocument2, IOleCommandTarget, and IDispatch). I would appreciate any help regarding this matter. Best Regards, Andrew ...

Looking for Missing Data
I have a spreadsheet on employee deductions. Since it's the beginning of the year I need to verify if every employee has a specific deduction and if it has been calculated correctly. My report is laid out as follows: EE Name Salary Deduction Code Deductiont Amount Sam $2000 A $120 Sam $2000 B $80 Sam $2000 C $10 Sam $2000 D $17 Sam $2000 W $43 Dave $2500 A $150 Dave $2000 B $80 Dave ...

Mail Merge with external data source
I am attempting to have an xml mail merge document automatically connect to an external database using the w:query and w:datasource settings. When I open the xml doc, Word 2008 gives me an error that it cannot find the data source. This works fine with Office 2003 and 2007 for windows, but I am having issues trying to get it to work with 2008 for mac. Is this even possible on the mac platform? Thanks for any input. Hi Myron: A data source for a mail merge operation can be an Excel sheet, the Office Address Book, a FileMaker Pro database, or a Word document. Sorry, nothin...

Transfer of Data Between Sheets
Hi All. I hope I can explain this clearly. Here is what I would like to do: I have created a large worksheet that contains, in part, all the projects I'm doing for different departments. Say, for example, I get a new project from sales. When I enter the new project details on the main worksheet and I type in "sales" under the "department" column, I want the whole row of data for that project copied to a new worksheet (within the workbook) titled "sales." If I get a project from legal, I want the data transferred to a worksheet called "legal" an...

copying data from folder to folder
I have folders on a server, one folder for each branch office. Within each branch folder, I have several folders labeled Warehouse, etc.. In the warehouse folder, I have several excel workbooks (one for each month, etc.), each comprised of 8 to 10 worksheets. If I add lines in one branch warehouse worksheet, can I have the other branch warehouse worksheets set up to copy that data? TIA You can have a "Master" workbook Then link the others to that one, so it can be updated when opened. "RPW" wrote: > I have folders on a server, one folder for each branch office...

Extracting data from one column
I have a column with the following data that I would like to extract into three columns: Current data in one column ------------------------------------- Berlin, Germany - 3,337,000 Riyadh, Saudi Arabia - 3,328,000 Desired result in three columns ------------------------------------- City Country Population Berlin Germany 3,337,000 Riyadh Saudi Arabia 3,328,000 Thanks, Craig Try the Text to Columns Wizard: Data/Text to Column. regards, Hans >-----Original Message----- >I have a column with the following data that I would like >to extract into ...

Converting Graph data links to values using code
Excel 97 I create charts using a workbook with templates and change the reports using dynamic data ranges. One worksheet can hold several graphs. I then copy the charts to another workbook for distribution. I extinguish the data series links by manually converting them to values (select graph line, F2, F9, Enter) etc., graph by graph. A painstaking job at times. Can this be automated with code? TIA WSF Hi have a look at http://peltiertech.com/Excel/Charts/chartvba.html#DelinkCht -- Regards Frank Kabel Frankfurt, Germany WSF wrote: > Excel 97 > I create charts using a workbook w...

Data grouping and averaging in excel 2003
I am looking into purchasing a data logger that will collect voltage, amperage with a time scale. Below is a sample of the data and a summary to be generated above the data. I have some problems with how I can have a msexcel spreadsheet automatically calculate averages and total times for each zone. I would like a summary for many separate zones with a dozen or so sessions that have recording to last from 10 to 100 seconds within each session and automatically disregard calculating any values in column C that are <30 & all rows in column B that have been disregarded in column C. Averagi...

Reducing Data Points in X-Y Scatter Graph
I have a series of data each with over 1000 points of data. I want to be able to pick the entire data set, but only plot say 200 to 300 hundred of them. I know I have done this, but I cannot find it. Isn't there a way to tell Excel to plot every so many other points? Scott - Here's one way: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Scott Barnhill wrote: > I have a series of data each with over 1000 points of dat...