Dynamic range chart losing its dynamism..??

Help,

have set up a named range using offset for the desired chart range,
this is a contiguous range with series in rows and the series names in
the first column (i can do without the xaxis labels for now)

the number of series is high, frm 20 - 150 ish (below the 256 max)

i create a stacked are using  a nondynamic range that looks as i want

then right click on chart, source data, type in data range the sheet
name and name range.

Thant works, gives me the data i want

But when i go back to the chart to review the source datam it loses
the formula and just gives me the 'hard-coded' range.

Any ideas on how to give my dynamic chart some dynamism??

0
philcuddy (20)
5/29/2007 4:32:28 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
429 Views

Similar Articles

[PageSpeed] 10

On 29 May, 17:32, philcud <philcu...@gmail.com> wrote:
> Help,
>
> have set up a named range using offset for the desired chart range,
> this is a contiguous range with series in rows and the series names in
> the first column (i can do without the xaxis labels for now)
>
> the number of series is high, frm 20 - 150 ish (below the 256 max)
>
> i create a stacked are using  a nondynamic range that looks as i want
>
> then right click on chart, source data, type in data range the sheet
> name and name range.
>
> Thant works, gives me the data i want
>
> But when i go back to the chart to review the source datam it loses
> the formula and just gives me the 'hard-coded' range.
>
> Any ideas on how to give my dynamic chart some dynamism??

BTW Excel 2003

0
philcuddy (20)
5/29/2007 4:38:17 PM
The data range does not work dynamically like this. It will convert the name 
into an address.

You can apply a dynamic range for the X and Y values of each series, tedious 
to set up for so many series, but robust and dynamic.

Or you could do some kind of macro that updates the data range. Assuming the 
name is "ChartDataRange" and the chart is the only one on the worksheet, 
right click on the sheet tab and select View Code. This pops up the code 
module for the sheet. In the left dropdown at the top of the module's 
window, select Worksheet. In the right dropdown, select Change. Now you have 
the following two empty event procedures:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

You can delete the Worksheet_SelectionChange procedure. Copy the middle of 
the following and paste it into your Worksheet_Change procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("ChartDataRange")) Is Nothing Then
    Me.ChartObjects(1).Chart.SetSourceData 
Source:=Me.Range("ChartDataRange")
  End If
End Sub

Whenever the worksheet changes, it runs this procedure. If the Target (the 
changed cell) is included in ChartDataRange, the chart's source data range 
is redefined as ChartDataRange.

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


"philcud" <philcuddy@gmail.com> wrote in message 
news:1180456348.672787.193670@q69g2000hsb.googlegroups.com...
> Help,
>
> have set up a named range using offset for the desired chart range,
> this is a contiguous range with series in rows and the series names in
> the first column (i can do without the xaxis labels for now)
>
> the number of series is high, frm 20 - 150 ish (below the 256 max)
>
> i create a stacked are using  a nondynamic range that looks as i want
>
> then right click on chart, source data, type in data range the sheet
> name and name range.
>
> Thant works, gives me the data i want
>
> But when i go back to the chart to review the source datam it loses
> the formula and just gives me the 'hard-coded' range.
>
> Any ideas on how to give my dynamic chart some dynamism??
> 


0
jonxlmvpNO (4558)
5/30/2007 3:28:19 AM
Cheers,

looks like i've misunderstood the dynamic part - perhaps a feature
request for a future version of excel??

TY

On May 30, 4:28 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> The data range does not work dynamically like this. It will convert the name
> into an address.
>
> You can apply a dynamic range for the X and Y values of each series, tedious
> to set up for so many series, but robust and dynamic.
>
> Or you could do some kind of macro that updates the data range. Assuming the
> name is "ChartDataRange" and the chart is the only one on the worksheet,
> right click on the sheet tab and select View Code. This pops up the code
> module for the sheet. In the left dropdown at the top of the module's
> window, select Worksheet. In the right dropdown, select Change. Now you have
> the following two empty event procedures:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> End Sub
>
> You can delete the Worksheet_SelectionChange procedure. Copy the middle of
> the following and paste it into your Worksheet_Change procedure:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>   If Not Intersect(Target, Me.Range("ChartDataRange")) Is Nothing Then
>     Me.ChartObjects(1).Chart.SetSourceData
> Source:=Me.Range("ChartDataRange")
>   End If
> End Sub
>
> Whenever the worksheet changes, it runs this procedure. If the Target (the
> changed cell) is included in ChartDataRange, the chart's source data range
> is redefined as ChartDataRange.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> "philcud" <philcu...@gmail.com> wrote in message
>
> news:1180456348.672787.193670@q69g2000hsb.googlegroups.com...
>
> > Help,
>
> > have set up a named range using offset for the desired chart range,
> > this is a contiguous range with series in rows and the series names in
> > the first column (i can do without the xaxis labels for now)
>
> > the number of series is high, frm 20 - 150 ish (below the 256 max)
>
> > i create a stacked are using  a nondynamic range that looks as i want
>
> > then right click on chart, source data, type in data range the sheet
> > name and name range.
>
> > Thant works, gives me the data i want
>
> > But when i go back to the chart to review the source datam it loses
> > the formula and just gives me the 'hard-coded' range.
>
> > Any ideas on how to give my dynamic chart some dynamism??


0
philcuddy (20)
5/30/2007 9:51:00 AM
You're better off learning to do it within the constraints of Excel, than 
waiting for a feature request to be fulfilled. If the new feature came out 
tomorrow, (a) it would work differently than you would have expected, and 
(b) it would be ten years before everyone using your workbook will have 
upgraded.

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


"philcud" <philcuddy@gmail.com> wrote in message 
news:1180518660.475444.115930@m36g2000hse.googlegroups.com...
> Cheers,
>
> looks like i've misunderstood the dynamic part - perhaps a feature
> request for a future version of excel??
>
> TY
>
> On May 30, 4:28 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>> The data range does not work dynamically like this. It will convert the 
>> name
>> into an address.
>>
>> You can apply a dynamic range for the X and Y values of each series, 
>> tedious
>> to set up for so many series, but robust and dynamic.
>>
>> Or you could do some kind of macro that updates the data range. Assuming 
>> the
>> name is "ChartDataRange" and the chart is the only one on the worksheet,
>> right click on the sheet tab and select View Code. This pops up the code
>> module for the sheet. In the left dropdown at the top of the module's
>> window, select Worksheet. In the right dropdown, select Change. Now you 
>> have
>> the following two empty event procedures:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> End Sub
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>
>> End Sub
>>
>> You can delete the Worksheet_SelectionChange procedure. Copy the middle 
>> of
>> the following and paste it into your Worksheet_Change procedure:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>   If Not Intersect(Target, Me.Range("ChartDataRange")) Is Nothing Then
>>     Me.ChartObjects(1).Chart.SetSourceData
>> Source:=Me.Range("ChartDataRange")
>>   End If
>> End Sub
>>
>> Whenever the worksheet changes, it runs this procedure. If the Target 
>> (the
>> changed cell) is included in ChartDataRange, the chart's source data 
>> range
>> is redefined as ChartDataRange.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. -http://PeltierTech.com
>> _______
>>
>> "philcud" <philcu...@gmail.com> wrote in message
>>
>> news:1180456348.672787.193670@q69g2000hsb.googlegroups.com...
>>
>> > Help,
>>
>> > have set up a named range using offset for the desired chart range,
>> > this is a contiguous range with series in rows and the series names in
>> > the first column (i can do without the xaxis labels for now)
>>
>> > the number of series is high, frm 20 - 150 ish (below the 256 max)
>>
>> > i create a stacked are using  a nondynamic range that looks as i want
>>
>> > then right click on chart, source data, type in data range the sheet
>> > name and name range.
>>
>> > Thant works, gives me the data i want
>>
>> > But when i go back to the chart to review the source datam it loses
>> > the formula and just gives me the 'hard-coded' range.
>>
>> > Any ideas on how to give my dynamic chart some dynamism??
>
> 


0
jonxlmvpNO (4558)
5/30/2007 11:52:49 AM
The simplest way I know to create "dynamic" charts is to create the chart 
with a macro.  Then all you have to do is have the specfic data page showing 
and run the macro, and waza, a chart is born (or perhaps click the top left 
cell of the "table".  You can record many features in the macro, like 
discovering the number of rows, the number of columns (with end down and end 
right macro instructions), and use a loop to explicitly define the series 
properly.  I have found that Microsoft's built in discovery isn't reliable 
enough.  

For existing charts, I have the chart showing/on top, and then run a macro 
and the chart gets formatted or "updated" with its "new" data.

"philcud" wrote:

> On 29 May, 17:32, philcud <philcu...@gmail.com> wrote:
> > Help,
> >
> > have set up a named range using offset for the desired chart range,
> > this is a contiguous range with series in rows and the series names in
> > the first column (i can do without the xaxis labels for now)
> >
> > the number of series is high, frm 20 - 150 ish (below the 256 max)
> >
> > i create a stacked are using  a nondynamic range that looks as i want
> >
> > then right click on chart, source data, type in data range the sheet
> > name and name range.
> >
> > Thant works, gives me the data i want
> >
> > But when i go back to the chart to review the source datam it loses
> > the formula and just gives me the 'hard-coded' range.
> >
> > Any ideas on how to give my dynamic chart some dynamism??
> 
> BTW Excel 2003
> 
> 
0
Chasville (1)
6/1/2007 6:22:00 PM
Reply:

Similar Artilces:

line chart base to a pivot chart
Hi All, I've just made a pivot table with two pages,28 data,and one row heading. I want to create some line charts on another sheets.how to do it?. I want every time I select a page from my pivot table,the chart will display data form the selected pages.If one chart can display all the data, the rest data will be displayed on another chart> Is it possible?Please advise me how to do it step by step. Thank's ...

Need shaded area in gantt chart to show lines around each grid squ
Hi - I have created a gantt chart using excel 2002. I have a shaded area on my chart from 9/1-9/10/07. Can I make the shaded area show lines through it? This would make it much easier to follow down the page on the date selected. I know it is easily done in excel when you fill in say 3 squares in yellow and then highlight the area and select borders. I tried doing this in my chart, but can not make it work. Any help would be appreciated. -- Karen W. ...

Dynamics.SET
GP should have the facility to change the .SET file settings depending on the company a user logged into. In this way different report formats, or forms layouts could be utilised for each company automatically. Currently, this can be done by having different icons pointing to specific dynamics.set and dictionary files. (Logged from case no 8330010) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Dynamic Charting Question
I'm attempting to set up a dynamic chart. So far, I've defined a name for cells B33:M33 (using the following offset equation). =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1) I'd like to be able to add more columns after M and the chart will automatically pick it up. We have a graph that currently includes B33:M33 as the X axis values for a line chart. How do I change the information in the chart to include the determined dynamic range for X? Thanks in advance, Barb Reinhardt I've figured out what I need to edit to get the series to change, but is there ...

Importing data from several sheets, to one chart
I need data from many sheet into one chart (In its own sheet). I know how to do it when its from from sheet (In this example, sheet named Pernille) : =Pernille!$AS$10 I tried this : =Pernille!$AS$10;Aase!$AS$10 But the reference was not valid. Please help, I havent been able to find anything, not even in my E-learning. Martin, Probably the easiest way to chart data from multiple sheets is to create a summary range in a single sheet that references the other sheets with formulas. If you use this technique then all you have to do is generate the chart using the summary range as your sourc...

Chart making
When I select a column of data to make a chart. It only takes first cell and leave all others. In chart shows only one value, why? Arun, Please try this first - if you are working with the Chart Wizard, make sure that you have the option "Series in Columns" selected as opposed to "Series in Rows". This input is available in step number 2 of the wizard under the Data Range tab. If this doesn't work can you post back with a sample of your data? -- Regards, John Mansfield http://www.pdbook.com "Arun Bhadoria" wrote: > When I select a column of da...

Dynamics RMS HQ Client keeps crashing
Hi everyone, We have an issue at the moment with Dynamics RMS HQ Client crashing while attempting to process a 401 Upload Data worksheet. I tried manually creating one and moving the from date back 30min with each one, but it gets to a point where it just crashes. As the day continues this time keeps advancing so it seems as if its something to do with the amount of data it is trying to upload. It does normally upload a lot more and thus I don't really understand why it is crashing now. Debugging has been switched on and it is crashing at the "Sending Worksheet ...

range where clause??
Hi, I am using the range where clause in my code. I just read that this works only with the SQL Server & not with other databases line Pervasive SQL & C-tree. If I use the range start & range end clauses would the code work for all the databases? I have completed the coding. What would you suggest at this point? Is it better to recode everything or is there any other solution? Thanks & Regards, Sup Hello sup, Since MBS that other databases arn't supported any longer, why would you need to build code to support it? Kind Regards Eddie Fourie MBS Specialist s> Hi,...

How can I set margins for headers on charts. The margin option ap.
I need indent chart headers to coincide with margins selected fopr the chart itself. I can not find any control in Excel or printer softeare that will indent a left-justified header. As you noted, there is no built-in chart option to indent a left justified header. I would suggest replacing the header with a text box. You can then use character spacing within the text box to create the look of an indented header. ---- Regards, John Mansfield http://www.pdbook.com "sayles1986" wrote: > I need indent chart headers to coincide with margins selected fopr the chart >...

Charting a linear equation
I have a chart in excel 2007, the X series is edited to be: ={0,150000} The Y series needs to be a formula, =2X+1000 How can I enter this in so it leverages X in its calculation. I know I can make cells that do the calcs, but cant this be written up in the series editor without using extra cells? Thanks! jlc On Fri, 27 Apr 2007, in microsoft.public.excel.charting, Joseph L. Casale <JCasale@newsgroup.nospam> said: >How can I enter this in so it leverages X in its calculation. I know I can >make cells that do the calcs, but cant this be written up in the series >edi...

Version 2002 - Org Chart - Creating using wizard with xls Data Source
Hi Guys, I'm creating an org chart using xls spreadsheet as the data source. Is there a way to sort the branches alphabetically automatically? Cheers Unfortunately, the Org Chart solution generates the diagram using a random order. -- Mark Nelson Office Graphics - Visio Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "u828576" <bitofpacedropshoulder@yahoo.com> wrote in message news:OxaL%23dwvHHA.1524@TK2MSFTNGP06.phx.gbl... > Hi Guys, > > I'm creating an org chart using xls spreadsheet as...

Area Charts
I am using an area chart to display data over the past 16 quaters and would like the ends of the chart touch the "Y" axis line. Currently the area chart begins in the middle of "X" axis point Q1. Can anyone help? Hi DavidZ, Double click the x axis to display the format dialog. On the Scale tab you need to uncheck the Value axis crosses between categories. Cheers Andy DavidZ wrote: > I am using an area chart to display data over the past 16 quaters and would like the ends of the chart touch the "Y" axis line. Currently the area chart begins in the middl...

losing line breaks
Hi when I import xml data into Access 2002 , text imported into a memo field loses the line breaks. For example I create in notepad the xml file <?xml version="1.0" encoding="iso-8859-1"?> <table> <record> <column> paragraph one paragraph two </column> </record> </table> but what appears in Access is paragraph one paragraph two I want to preserve the line breaks, any suggestion welcome. cheers David Hi David, When I import your sample XML into Access 2003 the white space is preserved; this is pasted from the Immediate pane:...

The quality of the network connection to the Microsoft Dynamics CRM server is poor
We have a gigabit connection to the CRM server and all servers are in the same LAN segment except for exchange and we always get the The quality of the network connection to the Microsoft Dynamics CRM server is poor when running the diagnostics even though our latency to the CRM server, the CRM DB Server, The AD server, Reporting Server, Exchange Server is under 1 ms. What could be causing it to complain about the network? Thanks. We had seen this warning on a few environments. It is only a warning, so we just monitor the performance and it works fine. Perhaps open a support ticket to hel...

Help with Scroll and Zoom Bar for Candle Chart
I am currently doing data manipulation for my series of data. I have Time, Open, High, Low, Close. There are approximately 6000 rows of data that I need to input into the Candle Chart and to do that I require a Scroll and Zoom function. I have been using a template for Scroll/Zoom for a normal basic Line Chart and attempted to reconfigure for a Candle Chart. I have named the following: SCROLLVALUE=Sheet1!$S$31 SERIES1==OFFSET(Sheet1!$B$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES2==OFFSET(Sheet1!$C$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES3==OFFSET(Sheet1!$D$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES4==OFFSET(Shee...

HELP, My chart dosen't work correctly.
I am working in Excell XP. I have a chart I need to do, but can't get i to work. The chart needs to have a list of text on the left, y axis and it needs to be JUSTIFIED text with text wrap. On the right needs t be a BAR chart showing survey question results. When I get the text to wrap the bars no longer line up with th questions. I am having a hard time with this. Then it must be able t print portrait style on an 8 1/2 x 11 sheet. Any help would be appreciated. Marshal ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet ...

Box and Whisker Charts
Has anyone ever made one? I've been cranking them out all afternoon. Here's my technique: http://peltiertech.com/Excel/Charts/BoxWhiskerH.html http://peltiertech.com/Excel/Charts/BoxWhiskerV.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Kwalters wrote: > Has anyone ever made one? ...

about radar chart #2
I am new in Excel please let me know about radar chart? for which propouse it will used ? let me some samples ? best You can search in Google for information. For example: http://www.skymark.com/resources/tools/radar_charts.asp http://www.jcrinc.com/subscribers/source.asp?durki=3849&site=48&return=3827 http://onyx.quadbase.com/ECServletDemo/RadarServletDemo.html hassan barjini wrote: > I am new in Excel please let me know about radar chart? for which propouse > it will used ? let me some samples ? > best -- Debra Dalgleish Excel FAQ, Tips & Book List ...

Variable Width 100% Stacked Column Chart
Hi, I need to make a variable width 100% stacked column chart (similar t the Column chart with varying width bars from andypope.info but 100 stacked) The x-axis would contain various market segments and the width would b proportional to their size, the y-axis divides each segment up by a attribute. Any ideas? Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Earl - Stephen Bullen has a Variable Width Column Chart example on his web site (http://bmsltd.co.uk), lo...

axis font excel chart?
how can I change the font of the scale in an excel chart? Right-click on the relevant axis. Format Axis/ Font/ Size: -- David Biddulph "ColinB" <ColinB@discussions.microsoft.com> wrote in message news:6B1096F8-87B3-4642-BCC8-0EEE11FE0DED@microsoft.com... > how can I change the font of the scale in an excel chart? ...

change font color of first cell in data table attached to chart
When I change the color of my font in a data table that I attached to my column chart, the first cell or what appears to be the name for my first column along the x-axis will not change color. WHY? Hi Momo, >When I change the color of my font in a data table that I attached to my >column chart, the first cell or what appears to be the name for my first >column along the x-axis will not change color. WHY? Because Excel does not work with the data cell formatting for its graphs but only with the data cells volumes... BTW: Afaik you can not use different colors within the x...

Upgrade 9 SP3 to 10 SP4
Ok so I installed 9 SP3, backed up, and then tried to install 10. Dynamics upgrades just fine, but then when it gets to the next screen where it updates the companies there are no companies to update. Since this is the case, my upgrade is stuck right here. I have a support incident in with Microsoft, but if I don't find an answer within an hour I'm going to be forced to roll them back so the customer can work the second half of the day. Anyone have any ideas? ...

Losing sheet protection in read only mode
Hi I have a workbook with 8 sheets which are all protected by a password (the same one) When i open the workbook normally, all sheets are protected, however if someone else has the workbook open and i open it in read only mode, the first sheet only is unprotected.(the rest are still protected) Can anyone shed any light on this problem? Paul. ...

How do I change an existing range in a pivot table?
I have new data to a worksheet and I would like the pivot table to include this data, but I guess when I first created the pivot table the range was set and now the data is outside this range. How do I change this range? Right click on the Pivot Table and choose "Wizard". Click "Back" until you can choose the range again. When you have reselected it, you can just press "Finish" -- Regards Juan Pablo Gonz´┐Żlez "steve" <steve@discussions.microsoft.com> wrote in message news:7D32D144-0701-40E4-B7B2-92EB9936EC14@microsoft.com... > I have new...

PWA "Gantt Chart Formats": Delay and Slippage
Hi, When I log into PWA-->Server Settings--> Look & Feel --> Gantt Chart Formats, under several formats there are bars named "Slippage", "Delay", "Early Schedule" and "Late Schedule". As we know, a bar should show a line between a Start Date and an End Date. Does anyone understand what these bars use as Start Date and End Date? Thanks, Barak Submitted via EggHeadCafe - Software Developer Portal of Choice ..NET Security [aPress] http://www.eggheadcafe.com/tutorials/aspnet/977c2770-c594-4859-b07c-b83d902e72cc/net-security-...