Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new range
So I have a chart that is based on data until october:
paid unpaid total paid vs unpaid january 1 65 66 2% february 3 5484 5487 0% march 564 2621 3185 18% april 561 56194 56755 1% may 516 548 1064 48% june 61 64 125 49% july 61 894894 894955 0% august 6 9494 9500 0% september 161 48949 49110 0% october 9616 949189 958805 1% november 0 #DIV/0! december 0 #DIV/0! Then I create a copy of this chart in new worksheet. However, when I update range of the original chart until December (specifing new range) with updated figures, the copy doesn't update itself and I have to also manually update the copy. Everything is fine with the copy when only figures change (without changing the range), but when a new range is specified then there is a problem. My newly created report has lots of graphs like that and I am doomed now. Is there a wa to hard link these charts?? I add that I don't do VBA but maybe if that would be only solution I could ask somebody from work to this if you have an idea. Any help appreciated. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new range
Each chart's ranges are independent of other charts' ranges. Say the series
formula says: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet2!$B$2: $B$10,1) If you change this to include down to row 12 in one chart, the other chart has no knowledge of this. If you were to define dynamic ranges, e.g., XData and YData, which counted the number of rows to use (or used a value you entered into a cell), then the series formula for both charts would say: =SERIES(Sheet1!$B$1,Sheet1!XData,Sheet2!YData,1) and both would always show the same data. Here is a blog entry about dynamic ranges as chart series source data: http://peltiertech.com/WordPress/200...ynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... So I have a chart that is based on data until october: paid unpaid total paid vs unpaid january 1 65 66 2% february 3 5484 5487 0% march 564 2621 3185 18% april 561 56194 56755 1% may 516 548 1064 48% june 61 64 125 49% july 61 894894 894955 0% august 6 9494 9500 0% september 161 48949 49110 0% october 9616 949189 958805 1% november 0 #DIV/0! december 0 #DIV/0! Then I create a copy of this chart in new worksheet. However, when I update range of the original chart until December (specifing new range) with updated figures, the copy doesn't update itself and I have to also manually update the copy. Everything is fine with the copy when only figures change (without changing the range), but when a new range is specified then there is a problem. My newly created report has lots of graphs like that and I am doomed now. Is there a wa to hard link these charts?? I add that I don't do VBA but maybe if that would be only solution I could ask somebody from work to this if you have an idea. Any help appreciated. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
Thank you Jon for your prompt response. I have just started reading your
website. The only think is that my data is in a table that is in turn based on cell reference to pivot table. So it is a massive table where I get my ranges from, which expands when the pivot table does. Therefore I am just not sure if I can use the solution from your website. I can e-mail a picture of what I mean if I complicated my description. The amount of graphs I had to create based on my data just crossed out typical formula approach that would gather everything in other tables. It was just to complex for me... However, thank you once again "Jon Peltier" wrote: Each chart's ranges are independent of other charts' ranges. Say the series formula says: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet2!$B$2: $B$10,1) If you change this to include down to row 12 in one chart, the other chart has no knowledge of this. If you were to define dynamic ranges, e.g., XData and YData, which counted the number of rows to use (or used a value you entered into a cell), then the series formula for both charts would say: =SERIES(Sheet1!$B$1,Sheet1!XData,Sheet2!YData,1) and both would always show the same data. Here is a blog entry about dynamic ranges as chart series source data: http://peltiertech.com/WordPress/200...ynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... So I have a chart that is based on data until october: paid unpaid total paid vs unpaid january 1 65 66 2% february 3 5484 5487 0% march 564 2621 3185 18% april 561 56194 56755 1% may 516 548 1064 48% june 61 64 125 49% july 61 894894 894955 0% august 6 9494 9500 0% september 161 48949 49110 0% october 9616 949189 958805 1% november 0 #DIV/0! december 0 #DIV/0! Then I create a copy of this chart in new worksheet. However, when I update range of the original chart until December (specifing new range) with updated figures, the copy doesn't update itself and I have to also manually update the copy. Everything is fine with the copy when only figures change (without changing the range), but when a new range is specified then there is a problem. My newly created report has lots of graphs like that and I am doomed now. Is there a wa to hard link these charts?? I add that I don't do VBA but maybe if that would be only solution I could ask somebody from work to this if you have an idea. Any help appreciated. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
I didn't mention that I use Excel version 2002. In my opinion this should be
addressed by MS as it would be much easier. Especially when you create an exact copy of the original (they should behave in a way like - what first has and does the second one repeats). "Piotr (Peter)" wrote: Thank you Jon for your prompt response. I have just started reading your website. The only think is that my data is in a table that is in turn based on cell reference to pivot table. So it is a massive table where I get my ranges from, which expands when the pivot table does. Therefore I am just not sure if I can use the solution from your website. I can e-mail a picture of what I mean if I complicated my description. The amount of graphs I had to create based on my data just crossed out typical formula approach that would gather everything in other tables. It was just to complex for me... However, thank you once again "Jon Peltier" wrote: Each chart's ranges are independent of other charts' ranges. Say the series formula says: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet2!$B$2: $B$10,1) If you change this to include down to row 12 in one chart, the other chart has no knowledge of this. If you were to define dynamic ranges, e.g., XData and YData, which counted the number of rows to use (or used a value you entered into a cell), then the series formula for both charts would say: =SERIES(Sheet1!$B$1,Sheet1!XData,Sheet2!YData,1) and both would always show the same data. Here is a blog entry about dynamic ranges as chart series source data: http://peltiertech.com/WordPress/200...ynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... So I have a chart that is based on data until october: paid unpaid total paid vs unpaid january 1 65 66 2% february 3 5484 5487 0% march 564 2621 3185 18% april 561 56194 56755 1% may 516 548 1064 48% june 61 64 125 49% july 61 894894 894955 0% august 6 9494 9500 0% september 161 48949 49110 0% october 9616 949189 958805 1% november 0 #DIV/0! december 0 #DIV/0! Then I create a copy of this chart in new worksheet. However, when I update range of the original chart until December (specifing new range) with updated figures, the copy doesn't update itself and I have to also manually update the copy. Everything is fine with the copy when only figures change (without changing the range), but when a new range is specified then there is a problem. My newly created report has lots of graphs like that and I am doomed now. Is there a wa to hard link these charts?? I add that I don't do VBA but maybe if that would be only solution I could ask somebody from work to this if you have an idea. Any help appreciated. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
The question arises, why do you need two copies of the same chart?
There is a possible solution, which involves the Camera tool. This allows you to take a picture of a range, and place the picture somewhere else. and it will always be an up-to-date picture of the range, no matter how you change it. Simply arrange the chart over a range, and use the camera to take a picture of the range below the chart. The camera tool has to be added to a toolbar somewhere. Right click on the toolbar area and choose Customize. On the Commands tab, choose Tools in the left list, then scroll 2/3 to the end of the right list, click on the Camera tool, and drag it to a convenient place on a toolbar. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... I didn't mention that I use Excel version 2002. In my opinion this should be addressed by MS as it would be much easier. Especially when you create an exact copy of the original (they should behave in a way like - what first has and does the second one repeats). "Piotr (Peter)" wrote: Thank you Jon for your prompt response. I have just started reading your website. The only think is that my data is in a table that is in turn based on cell reference to pivot table. So it is a massive table where I get my ranges from, which expands when the pivot table does. Therefore I am just not sure if I can use the solution from your website. I can e-mail a picture of what I mean if I complicated my description. The amount of graphs I had to create based on my data just crossed out typical formula approach that would gather everything in other tables. It was just to complex for me... However, thank you once again "Jon Peltier" wrote: Each chart's ranges are independent of other charts' ranges. Say the series formula says: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet2!$B$2: $B$10,1) If you change this to include down to row 12 in one chart, the other chart has no knowledge of this. If you were to define dynamic ranges, e.g., XData and YData, which counted the number of rows to use (or used a value you entered into a cell), then the series formula for both charts would say: =SERIES(Sheet1!$B$1,Sheet1!XData,Sheet2!YData,1) and both would always show the same data. Here is a blog entry about dynamic ranges as chart series source data: http://peltiertech.com/WordPress/200...ynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... So I have a chart that is based on data until october: paid unpaid total paid vs unpaid january 1 65 66 2% february 3 5484 5487 0% march 564 2621 3185 18% april 561 56194 56755 1% may 516 548 1064 48% june 61 64 125 49% july 61 894894 894955 0% august 6 9494 9500 0% september 161 48949 49110 0% october 9616 949189 958805 1% november 0 #DIV/0! december 0 #DIV/0! Then I create a copy of this chart in new worksheet. However, when I update range of the original chart until December (specifing new range) with updated figures, the copy doesn't update itself and I have to also manually update the copy. Everything is fine with the copy when only figures change (without changing the range), but when a new range is specified then there is a problem. My newly created report has lots of graphs like that and I am doomed now. Is there a wa to hard link these charts?? I add that I don't do VBA but maybe if that would be only solution I could ask somebody from work to this if you have an idea. Any help appreciated. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
Hi Jon and thank you for your response,
When I was creating all my pivot tables and charts based on the cell reference table I had to have all the charts created before I started creating a report as it was made from the scratch and even my boss didn't exactley know what she wanted on the final report. Therefore, I needed all sort of charts before placing their copies on to the report and manipulating their order, place etc. My initial thought was, that once I had new sort of data I would put new range in the originals and then copies would be updated automatically as the originals are in the some workseets as tables so it is easier to navigate. Now I think due to linking like problem I will be just updating copies and abandon the originals. I wanted to automate the process of doing this report and similar tables could be achieved by using in most instances suproduct function with several conditions but even with pivot tables that report was time consuming so didn't really consider formulas as this would take much more time. Yet I think to give it a go and see how it goes. I need this report to show invoicing in the company for different businesses where we distict service A per country a b c... in months and sometimes it just a service as total in the following months. So It looks like a chart "service A-country a" and month Jan Feb...current month. Then I show total for Service A in Jan.....current month. One report is for Head of Depts and one collating everything for Heads of Division. I have over 100 charts so creating speparate tables was tiresome when using a formula that worked but I had to amend little details which I didn't have to in pivots. Additionaly I have to include tables that show, on top of graphs, paid, unpaid invoices and their numbers, receivable days etc. but this easy. So writing some VBA where my report is not static but changes (page breaks, bigger, smaller invoicing tables (the easy ones) so I can't just aks somebody to write a code that will replace/add data and correctly place everything again and again. Possibly I wrong with this VBA nature as I just don't do VBA at all. Regards, Piotr I know this is a long story but hopefully this will give you better overview. "Jon Peltier" wrote: The question arises, why do you need two copies of the same chart? There is a possible solution, which involves the Camera tool. This allows you to take a picture of a range, and place the picture somewhere else. and it will always be an up-to-date picture of the range, no matter how you change it. Simply arrange the chart over a range, and use the camera to take a picture of the range below the chart. The camera tool has to be added to a toolbar somewhere. Right click on the toolbar area and choose Customize. On the Commands tab, choose Tools in the left list, then scroll 2/3 to the end of the right list, click on the Camera tool, and drag it to a convenient place on a toolbar. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
Hi Jon,
Ok so I have read about 6 different tutorials including yours about dynamic ranges, however as an intermediate user I am still confused. I have a table G39:K50: Invoices [GBP] Payments [GBP] Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs. Invoiced 1 2008 26,643.26 26,643.26 0.00 100% 2 2008 17,596.49 17,596.49 0.00 100% 3 2008 45,565.64 31,987.15 13,578.49 70% 4 2008 34,258.51 22,766.73 11,491.78 66% 5 2008 30,873.22 18,138.02 12,735.20 59% 6 2008 39,550.61 22,695.54 16,855.06 57% 7 2008 37,848.49 11,041.48 26,807.01 29% (blank) 0.00 0.00 0.00 #DIV/0! (blank) 0.00 0.00 0.00 #DIV/0! This table ends in a row G50 where December will appear. All cells contain formulas that either displays month and a year when found in a source or sum up specified values in a range, so there are "blanks" and "zeros' as the formulas have been copied down in order to show dispalay new month and sum up what I need. Having done little exercise when creating a Dynamic Range according www.contextures.on.ca/xlNames01.html I could see the expanded range when I typed new name. Fantastic!. Nonetheless, what I would like to achieve is whenever new values appear automatically (as formulas will find them in a table), then the range is expanded. One of my ranges looks as follows: Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(tabl e!$G$39:$G$45),1) G is obvoiusly changed for H, I, J, K. I was just to confused with this formula when using Named Ranges so I left it as it is. My concern though, is my source data for a chart: Payment Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45, Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary X)=table!$G$39:$G$45. When new data appears in a table found by my formulas, the ranges do not expand. I am doing something wrong here. Possibly I should have change the ranges all the way down to a row 50 but when I did that, also no avail.I can't add every single time data by hand as I said I have over 100 charts and I would like to create a tool that will save my time. Aslo all my charts will be "chart sheets". I spent all my day trying to sort this out and I just can't get this right. I fyou could assist me again I would be obliged. Sorry for the long post but I wanted to explain this correctly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
copy chart & colors change | Charts and Charting in Excel | |||
Cannot change criteria on copied chart without changing original c | Charts and Charting in Excel | |||
making copied cells change with change in original cell | Excel Worksheet Functions | |||
Why does Excel Copy and Paste change dates from the original when. | Excel Discussion (Misc queries) |