A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic graph turning static



 
 
Thread Tools Display Modes
  #1  
Old February 5th, 2004, 09:08 PM
Joey Leake
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

I'm having trouble with a graph I'm trying to make dynamic.

I made a named value "bottomright" that returns a reference to the bottom
right cell of my data set. bottomright = OFFSET('Foo'!$B$2,
COUNTA('Foo'!$A:$A)-2, 3, 1, 1). That much works nicely.

The trouble I'm running into is in defining the data range for my graph. I
set the data range to ='Foo'!$B$1:bottomright, and it makes a nice graph
with exactly the data I want in it. When I add data, though, the graph
doesn't update. I go back and check the data range, and it has switched my
variable "bottomright" with a static reference to the cell it pointed to
when I initially set up the graph.

Is there some way to have the data range leave this dynamic? Thanks!


-Joey


  #2  
Old February 6th, 2004, 03:21 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

How are you setting the data range? Is it programmatically through
some property/method? Or is it through the GUI? If the former, I
believe XL/VBA is translating the 'bottomright' into the current value.
If the latter, I would love to know how, because I cannot set that
through the Chart Wizard.

Bottom line: I don't believe it is possible to specify a multi-column
range through a named dynamic formula. You can establish *up front* a
range for a single column (see the Dynamic Charts page of my web site)
and specify other ranges relative to this column. The examples on the
Dynamic Charts page show how.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm having trouble with a graph I'm trying to make dynamic.

I made a named value "bottomright" that returns a reference to the bottom
right cell of my data set. bottomright = OFFSET('Foo'!$B$2,
COUNTA('Foo'!$A:$A)-2, 3, 1, 1). That much works nicely.

The trouble I'm running into is in defining the data range for my graph. I
set the data range to ='Foo'!$B$1:bottomright, and it makes a nice graph
with exactly the data I want in it. When I add data, though, the graph
doesn't update. I go back and check the data range, and it has switched my
variable "bottomright" with a static reference to the cell it pointed to
when I initially set up the graph.

Is there some way to have the data range leave this dynamic? Thanks!


-Joey



  #3  
Old February 6th, 2004, 04:51 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

You can set the source data range by referring to a named range, but as
soon as you dismiss the dialog, Excel converts it to the absolute
address of the range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:

How are you setting the data range? Is it programmatically through
some property/method? Or is it through the GUI? If the former, I
believe XL/VBA is translating the 'bottomright' into the current value.
If the latter, I would love to know how, because I cannot set that
through the Chart Wizard.

Bottom line: I don't believe it is possible to specify a multi-column
range through a named dynamic formula. You can establish *up front* a
range for a single column (see the Dynamic Charts page of my web site)
and specify other ranges relative to this column. The examples on the
Dynamic Charts page show how.


  #4  
Old February 6th, 2004, 04:53 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

You could use the ChartObjects(1).Chart.SetSourceData with the named
range, and put it into a worksheet_change event, so it updates itself.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Joey Leake wrote:
I'm having trouble with a graph I'm trying to make dynamic.

I made a named value "bottomright" that returns a reference to the bottom
right cell of my data set. bottomright = OFFSET('Foo'!$B$2,
COUNTA('Foo'!$A:$A)-2, 3, 1, 1). That much works nicely.

The trouble I'm running into is in defining the data range for my graph. I
set the data range to ='Foo'!$B$1:bottomright, and it makes a nice graph
with exactly the data I want in it. When I add data, though, the graph
doesn't update. I go back and check the data range, and it has switched my
variable "bottomright" with a static reference to the cell it pointed to
when I initially set up the graph.

Is there some way to have the data range leave this dynamic? Thanks!


-Joey



  #5  
Old February 6th, 2004, 05:25 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

Not the way the OP is setting it, i.e., ='Foo'!$B$1:bottomright

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
You can set the source data range by referring to a named range, but as
soon as you dismiss the dialog, Excel converts it to the absolute
address of the range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:

How are you setting the data range? Is it programmatically through
some property/method? Or is it through the GUI? If the former, I
believe XL/VBA is translating the 'bottomright' into the current value.
If the latter, I would love to know how, because I cannot set that
through the Chart Wizard.

Bottom line: I don't believe it is possible to specify a multi-column
range through a named dynamic formula. You can establish *up front* a
range for a single column (see the Dynamic Charts page of my web site)
and specify other ranges relative to this column. The examples on the
Dynamic Charts page show how.



  #6  
Old February 6th, 2004, 05:45 PM
Joey Leake
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

I'm just using the chart wizard, switching to the Data Range tab, choosing
Columns, and setting the data range to ='Foo'!$B$1:bottomright and it works,
it just becomes an absolute value.

"Tushar Mehta" wrote in message
news:MPG.1a8d6ce8bf8fd5059896a5@news-server...
How are you setting the data range? Is it programmatically through
some property/method? Or is it through the GUI? If the former, I
believe XL/VBA is translating the 'bottomright' into the current value.
If the latter, I would love to know how, because I cannot set that
through the Chart Wizard.

Bottom line: I don't believe it is possible to specify a multi-column
range through a named dynamic formula. You can establish *up front* a
range for a single column (see the Dynamic Charts page of my web site)
and specify other ranges relative to this column. The examples on the
Dynamic Charts page show how.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm having trouble with a graph I'm trying to make dynamic.

I made a named value "bottomright" that returns a reference to the

bottom
right cell of my data set. bottomright = OFFSET('Foo'!$B$2,
COUNTA('Foo'!$A:$A)-2, 3, 1, 1). That much works nicely.

The trouble I'm running into is in defining the data range for my graph.

I
set the data range to ='Foo'!$B$1:bottomright, and it makes a nice graph
with exactly the data I want in it. When I add data, though, the graph
doesn't update. I go back and check the data range, and it has switched

my
variable "bottomright" with a static reference to the cell it pointed to
when I initially set up the graph.

Is there some way to have the data range leave this dynamic? Thanks!


-Joey





  #7  
Old February 7th, 2004, 03:04 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Dynamic graph turning static

Ah, yes! Thanks. Normally, when doing anything but the most basic
stuff I switch to the Series tab in the 2nd step of the Chart Wizard.
And, in there ='Foo'!$B$1:bottomright doesn't work. As far as the Data
Range tab goes, you are absolutely right. XL does accept a name and
then promptly changes it to the current absolute address.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
kstreamdotcom says...
I'm just using the chart wizard, switching to the Data Range tab, choosing
Columns, and setting the data range to ='Foo'!$B$1:bottomright and it works,
it just becomes an absolute value.

"Tushar Mehta" wrote in message
news:MPG.1a8d6ce8bf8fd5059896a5@news-server...
How are you setting the data range? Is it programmatically through
some property/method? Or is it through the GUI? If the former, I
believe XL/VBA is translating the 'bottomright' into the current value.
If the latter, I would love to know how, because I cannot set that
through the Chart Wizard.

Bottom line: I don't believe it is possible to specify a multi-column
range through a named dynamic formula. You can establish *up front* a
range for a single column (see the Dynamic Charts page of my web site)
and specify other ranges relative to this column. The examples on the
Dynamic Charts page show how.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm having trouble with a graph I'm trying to make dynamic.

I made a named value "bottomright" that returns a reference to the

bottom
right cell of my data set. bottomright = OFFSET('Foo'!$B$2,
COUNTA('Foo'!$A:$A)-2, 3, 1, 1). That much works nicely.

The trouble I'm running into is in defining the data range for my graph.

I
set the data range to ='Foo'!$B$1:bottomright, and it makes a nice graph
with exactly the data I want in it. When I add data, though, the graph
doesn't update. I go back and check the data range, and it has switched

my
variable "bottomright" with a static reference to the cell it pointed to
when I initially set up the graph.

Is there some way to have the data range leave this dynamic? Thanks!


-Joey






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.