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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
The formulas under the linked data are messing you up, I fear. Could you put
the totals above the range, or below the entire range, not three cells below the last month? If you know how many months of data you have, use that number in place of COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define it once, then define the others like so: Name: FirstRange Refers To: =OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45), 1) Name: SecondRange RefersTo: =OFFSET(FirstRange,0,1) Name: ThirdRange RefersTo: =OFFSET(FirstRange,0,2) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
Hi Jon,
Well, I moved away Gross Amount since I don't have any TOTALS below my ranges. Please have a look here for better overview: http://img398.imageshack.us/my.php?i...drangesqk9.jpg (obviously SUMPRODUCT formula has just different conditionals for each column) As you can see in the picture my ranges look as you told me to do so aside from COUNTA (I have put there a number of rows rather than COUNTA) but no avail anyway when formulas "enter " values. I suspect the culprit to be formulas since thay show 0.00 when no data is available and the date is "blank" because the formula is defined to do so. Maybe there is a way to "REFRESH THE RANGES" like in a web browser by pressing F5??:) and then it will trigger them:) I don't what to do now. Every single time I had"created" new values in a source worksheet and formulas showed them, ranges didn't expand. Any furhter suggestions Jon, as I still have a hope:)? Thanks, Piotr "Jon Peltier" wrote: The formulas under the linked data are messing you up, I fear. Could you put the totals above the range, or below the entire range, not three cells below the last month? If you know how many months of data you have, use that number in place of COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define it once, then define the others like so: Name: FirstRange Refers To: =OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45), 1) Name: SecondRange RefersTo: =OFFSET(FirstRange,0,1) Name: ThirdRange RefersTo: =OFFSET(FirstRange,0,2) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
This has been going on long enough. Email me your workbook and I'll set up
the dynamic ranges. jon at peltier tech dot com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... Hi Jon, Well, I moved away Gross Amount since I don't have any TOTALS below my ranges. Please have a look here for better overview: http://img398.imageshack.us/my.php?i...drangesqk9.jpg (obviously SUMPRODUCT formula has just different conditionals for each column) As you can see in the picture my ranges look as you told me to do so aside from COUNTA (I have put there a number of rows rather than COUNTA) but no avail anyway when formulas "enter " values. I suspect the culprit to be formulas since thay show 0.00 when no data is available and the date is "blank" because the formula is defined to do so. Maybe there is a way to "REFRESH THE RANGES" like in a web browser by pressing F5??:) and then it will trigger them:) I don't what to do now. Every single time I had"created" new values in a source worksheet and formulas showed them, ranges didn't expand. Any furhter suggestions Jon, as I still have a hope:)? Thanks, Piotr "Jon Peltier" wrote: The formulas under the linked data are messing you up, I fear. Could you put the totals above the range, or below the entire range, not three cells below the last month? If you know how many months of data you have, use that number in place of COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define it once, then define the others like so: Name: FirstRange Refers To: =OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45), 1) Name: SecondRange RefersTo: =OFFSET(FirstRange,0,1) Name: ThirdRange RefersTo: =OFFSET(FirstRange,0,2) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Piotr (Peter)" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
A copy of a chart doesn't change when the original has new ran
Below there is a correspondence between Jon and myself as I couldn't show
exactely what I need. Jon helped anyway and here are his answers so others can possibly benefit it future (I hope you don't mind Jon:))): Piotr - You have defined Period_Invoiced as =OFFSET(table!$H$9,0,0,COUNTA(table!$H$9:$H$17),1) What if you define it as =OFFSET(table!$H$9,0,0,COUNTA(table!$H$9:$H$20),1) This will enable the range to include the last three months. - Jon Hi Jon, Thank you for this hint. I have amended the range to row 20 and hey presto!! it works:) However, what I don't like or rather my boss are these 2 empty months visible on the chart (Nov and Dec) after I added Oct. Is there anyway to get away with it? If I could hide those months without the data that would be fantastic. Though, I need this chart to look as it looked before the amendment in a form you originally received. Let's say some slide bar or something ( I have seen some examples but I will lose my secondary axis I am affraid). What could you suggest? Kind regards, Piotr Yes, my first suggestion counts any cell with any content, including a formula that returns "". This should do it finally: =OFFSET(table!$H$9,0,0,SUM(IF(LEN(table!H9:H20)0, 1,0)),1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
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) |