Dynamic graph turning static

  • Thread starter Thread starter Joey Leake
  • Start date Start date
J

Joey Leake

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
 
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
 
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
 
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
 
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
 
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.
 
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
 
Back
Top