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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#6
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|