How do I copy a graph to new worksheet and get data from new sheet?

When I copy a worksheet with a graph or just copy a graph and paste in
a new worksheet, the source data is still linked to the original
worksheet. 

I've tried editing the sorce data to remove the sheet name to try to
make it relative to the current sheet, but that gives me "Error in
your formula".

Is there a quick way to get the graph to link to the data in the new
worksheet? Even a macro would help, if that's what it takes.

Thanx in advance...
0
9/11/2007 3:33:16 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
829 Views

Similar Articles

[PageSpeed] 35

If the chart is embedded in the worksheet, you should be able to copy the 
worksheet elsewhere, and the copied chart will point to the data on the 
copied sheet. Replace this sheet's data with the new set of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Elmer Smurdley" <ElmerdeletethispartSmurdley@hotmail.com> wrote in message 
news:9ucde354f3abr1h8nf79ln99qaiogojhjk@4ax.com...
> When I copy a worksheet with a graph or just copy a graph and paste in
> a new worksheet, the source data is still linked to the original
> worksheet.
>
> I've tried editing the sorce data to remove the sheet name to try to
> make it relative to the current sheet, but that gives me "Error in
> your formula".
>
> Is there a quick way to get the graph to link to the data in the new
> worksheet? Even a macro would help, if that's what it takes.
>
> Thanx in advance... 


0
jonxlmvpNO (4558)
9/11/2007 6:56:44 PM
Nope. I highlight the worksheet and copy to a blank worksheet. The
graph copies but the source data for the sheet in the new sheet still
points to the original sheet.

On Tue, 11 Sep 2007 14:56:44 -0400, "Jon Peltier"
<jonxlmvpNO@SPAMpeltiertech.com> wrote:

>If the chart is embedded in the worksheet, you should be able to copy the 
>worksheet elsewhere, and the copied chart will point to the data on the 
>copied sheet. Replace this sheet's data with the new set of data.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Tutorials and Custom Solutions
>Peltier Technical Services, Inc. - http://PeltierTech.com
>_______
>
>
>"Elmer Smurdley" <ElmerdeletethispartSmurdley@hotmail.com> wrote in message 
>news:9ucde354f3abr1h8nf79ln99qaiogojhjk@4ax.com...
>> When I copy a worksheet with a graph or just copy a graph and paste in
>> a new worksheet, the source data is still linked to the original
>> worksheet.
>>
>> I've tried editing the sorce data to remove the sheet name to try to
>> make it relative to the current sheet, but that gives me "Error in
>> your formula".
>>
>> Is there a quick way to get the graph to link to the data in the new
>> worksheet? Even a macro would help, if that's what it takes.
>>
>> Thanx in advance... 
>
0
9/27/2007 5:38:02 PM
No. Don't copy the cells of the sheet. Copy the sheet.

Right click on the sheet tab, and follow Move or Copy to make a copy of the 
sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Elmer Smurdley" <elmersmurdley@hotmail.com> wrote in message 
news:dgqnf3p0glfilr0isr8hkhsqjf2nnoa3j9@4ax.com...
> Nope. I highlight the worksheet and copy to a blank worksheet. The
> graph copies but the source data for the sheet in the new sheet still
> points to the original sheet.
>
> On Tue, 11 Sep 2007 14:56:44 -0400, "Jon Peltier"
> <jonxlmvpNO@SPAMpeltiertech.com> wrote:
>
>>If the chart is embedded in the worksheet, you should be able to copy the
>>worksheet elsewhere, and the copied chart will point to the data on the
>>copied sheet. Replace this sheet's data with the new set of data.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Tutorials and Custom Solutions
>>Peltier Technical Services, Inc. - http://PeltierTech.com
>>_______
>>
>>
>>"Elmer Smurdley" <ElmerdeletethispartSmurdley@hotmail.com> wrote in 
>>message
>>news:9ucde354f3abr1h8nf79ln99qaiogojhjk@4ax.com...
>>> When I copy a worksheet with a graph or just copy a graph and paste in
>>> a new worksheet, the source data is still linked to the original
>>> worksheet.
>>>
>>> I've tried editing the sorce data to remove the sheet name to try to
>>> make it relative to the current sheet, but that gives me "Error in
>>> your formula".
>>>
>>> Is there a quick way to get the graph to link to the data in the new
>>> worksheet? Even a macro would help, if that's what it takes.
>>>
>>> Thanx in advance...
>> 


0
jonxlmvpNO (4558)
9/27/2007 6:06:23 PM
Don't highlight the sheet, copy the contents, and paste onto a blank sheet.
Instead, select the sheet and then either right-click on the sheet's tab to 
select Move or Copy, or use the Edit menu to go to Move or Copy Sheet.  Tell 
it where you want the new sheet & ensure that you've selected to create a 
copy.
-- 
David Biddulph

"Elmer Smurdley" <elmersmurdley@hotmail.com> wrote in message 
news:dgqnf3p0glfilr0isr8hkhsqjf2nnoa3j9@4ax.com...
> Nope. I highlight the worksheet and copy to a blank worksheet. The
> graph copies but the source data for the sheet in the new sheet still
> points to the original sheet.
>
> On Tue, 11 Sep 2007 14:56:44 -0400, "Jon Peltier"
> <jonxlmvpNO@SPAMpeltiertech.com> wrote:
>
>>If the chart is embedded in the worksheet, you should be able to copy the
>>worksheet elsewhere, and the copied chart will point to the data on the
>>copied sheet. Replace this sheet's data with the new set of data.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Tutorials and Custom Solutions
>>Peltier Technical Services, Inc. - http://PeltierTech.com
>>_______
>>
>>
>>"Elmer Smurdley" <ElmerdeletethispartSmurdley@hotmail.com> wrote in 
>>message
>>news:9ucde354f3abr1h8nf79ln99qaiogojhjk@4ax.com...
>>> When I copy a worksheet with a graph or just copy a graph and paste in
>>> a new worksheet, the source data is still linked to the original
>>> worksheet.
>>>
>>> I've tried editing the sorce data to remove the sheet name to try to
>>> make it relative to the current sheet, but that gives me "Error in
>>> your formula".
>>>
>>> Is there a quick way to get the graph to link to the data in the new
>>> worksheet? Even a macro would help, if that's what it takes.
>>>
>>> Thanx in advance...
>> 


0
David
9/27/2007 6:26:40 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

Worksheet Auto update
I need to find a way to automate a process. Is there a way to automatically replace the content of a worksheet with the content of another one? Every morning I get a sales report in excel for the previous day sales. I save it in a folder and then I do a pivot table on this sheet to determine sales by product category for example. The following day, I open the previous day file, replace the sales report with the new one and then refresh my pivot table. Is there a way to have my sales report update anytime I get a new sales report? To be more clear I have a workbook with two tabs: Pivot...

How can I get 32-bit Integers?
I am using Access MS Office 2007 with VBA 6.5, on Win XP. My Integer type is just 16 bits, i.e. its max value is 32,767. How can I configure it so as to have 32-bit Integers, along with 64-bit Longs? "Renny Bosch" <noname@nospam.com> wrote in message news:OqqNyqmuKHA.4492@TK2MSFTNGP05.phx.gbl... > I am using Access MS Office 2007 with VBA 6.5, on Win XP. My Integer type > is just 16 bits, i.e. its max value is 32,767. How can I configure it so > as to have 32-bit Integers, along with 64-bit Longs? An Access Integer is 16 bits. A Long Integer is...

INTERNET MILLION DOLLARS
INTERNET MILLION DOLLARS - www.InternetMillionDollars.biz Watch the VIDEO on YOUTUBE... NOW! http://www.youtube.com/watch?v=dXQUjk5EGV0 MAKE MONEY INSTANTLY using 2007 featured INTERNET MILLION DOLLARS as a resource. WORK FROM HOME HOMEBASED JOBS AND IDEAS BUSINESS OPPORTUNITY Professor James Bradley's "Quick and real way to make BIG MONEY on the Internet!" The Independent Finally you have FOUND IT! Make a FORTUNE each month... .... every month! 100% ON AUTOPILOT Read it NOW and CHANGE YOUR LIFE! Are you fed up with being short of MONEY all the time? Do you DREAM about a...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

Copy Toolbar
Can someone tell me if there is a way to copy a toolbar I created on my computer to another computer? Thanks for your help! --- Message posted from http://www.ExcelForum.com/ The toolbar settings are in the *.xlb file. -- Regards, Peo Sjoblom "HelpMe >" <<HelpMe.zmcsh@excelforum-nospam.com> wrote in message news:HelpMe.zmcsh@excelforum-nospam.com... > Can someone tell me if there is a way to copy a toolbar I created on my > computer to another computer? Thanks for your help! > > > --- > Message posted from http://www.ExcelForum.com/ > You...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

getting started
I'm installing office 2003, outlook, I'm lost from server type to URL address to server info? I have sbc yahoo dsl. can you help me? damselindistress <damselindistress@discussions.microsoft.com> wrote: > I'm installing office 2003, outlook, I'm lost from server type to URL > address to server info? I have sbc yahoo dsl. can you help me? When you obtained your packet from SBC, they included all that information. Moreover, it's in the Help section of SBC Yahoo's site. The most likely settings are "pop.sbcglobal.yahoo.com" for the incoming serv...

how do you get a landscape page?
Please help, desperate! If you are using 2010, Page Design tab, Size, More pre-set page sizes. Letter landscape is the first choice. In all other Publisher versions landscape will be on the page setup screen found in the File menu. -- Mary Sauer http://msauer.mvps.org/ "becky.x" <becky.x@discussions.microsoft.com> wrote in message news:25D3EDBC-93EF-4582-94B2-83C9CE121E17@microsoft.com... > Please help, desperate! ...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Copy HYPERLINKS
I have about 200 entries in col a Some of these entries are hyperlinks to other locations I would like a macro to copy ONLY the hyperlinks from Col a to Col b Currently the hyperlinked items are interspaced over the entire col a, I would like to have all the hyperlinks moved to Col B one under the other with no spaces. Thanks Are they =HYPERLINK functions or Inserted hyperlinks?? -- Gary''s Student - gsnu200750 "pcor" wrote: > I have about 200 entries in col a > Some of these entries are hyperlinks to other locations > I would like a macro to copy ONLY the...

how to generate a polynomial regression graph with 90% CI?
How do I add a trend line with the 10th, 50th and 90th percentiles to a scattergram graph? Excel has no graphics option nor any worksheet function that will do this. LINEST will give the standard errors of regression coefficients, but not their covariances (which are needed to get the standard error of a fitted value). Your options are to either write your own polynomial regression function, or use a different package. R is a freely available http://www.r-project.org full-featured and widely used statistical package that is an open-source implementation of the S languaguage, that ca...

Carbon Copy of a message
when ever I get an email from 2 specific users the message always Carbon Copies itself and I cant figure out why. So I end up with 2 of the same messages. Any help would rock Thanx ...

Setting Defaults in New Item Wizard
How can I set a default value in the New Item Wizard? I want to always use a barcode type that is about 10 down from the top and I want Taxable Item to always be checked. Help... Good question, I need an answer too. "A StanTech Associate" wrote: > How can I set a default value in the New Item Wizard? I want to always use a > barcode type that is about 10 down from the top and I want Taxable Item to > always be checked. Help... You can set the default sales tax under Manager, File, Configuration, Sales Tax, Default item tax group. To set a default barcode type yo...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

new activity not in the appropriate queue
Help! I installed the Rollup 3 for MS CRM 4 and since then all created activities are redirected to the users assigned folder in queue and not in progress(my current activities) folder! It's not logical because a new activity is not yet assigned to a different user to be in the assigned folder !! I can't believe this is on purpose and I DON'T have a workflow on this. Can someone tell me how to fix this? ...

Find tab in worksheet
I have a workbook with many tabs & many users and would like to create a 'Go to / find' function that finds a particular tab when opening workbook, so that user will enter tab in text box and will then go directly to tab Try any one of these macros.. You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected mac...

modifying and copying multiply selections
Why when I select multiply objects, including text boxes or when I try to select them and then drag to a different locations the program just create a new copy but leaves the old one behind it? It didn't happen when I was working with other versions of Power Point. I think http://www.pptfaq.com/FAQ00882.htm will help. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Slav" <Sl...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...