Dynamic data ranges and Charts
 

Hi,

I have six charts displayed on a page with buttons
representing product categories on the left side of the
worksheet. The data supporting the graphs is on a
different worksheet.

When I click on one of the the buttons I need to update
all six graphs and change their data ranges to point to
different rows on the worksheet containing the data. I
have tried using a number of different methods with no
success, I either get subscript out of range or 1004
error. Anybody know how to do this?

Thanks

Keith

Dynamic data ranges and Charts
 

Hi Keith,

When I click on one of the the buttons I need to update
all six graphs and change their data ranges to point to
different rows on the worksheet containing the data. I

I usually use named ranges for the graphs. instead of changing the
properties of the graph i manipulate the definition of the named range.
usually, this works with formulas (no macros!) and the =OFFSET() function to
define the named range. So, you have to know where the starting point of
your data is and how many rows (or columns) you have.

eg: you have a spinbutton that gives you numbers 1, 2, 3, 4.. to cell
"myrange", in cell "nrRows" you have the number of rows that changes
according to "myrange". (you have a table that has 1,2,3 etc and a
count-function to count your records and you use vlookup. you could have the
nr. of records at the top of your data and you use another offset-function
to find the correct value). your data is in colums A through D, with the
spinbutton you define which colum it is. the definition of range "myYData"
would be: = OFFSET(A1, 0, myRange-1, nrRows, 1). in your chart you define
the y-range as =sheet1!myYdata (make a reference to the workbook or sheet, I
do not remember now, the name alone will not work).

best regards

arno

Dynamic data ranges and Charts
 

Arno's approach is my preferred way to do this, too. I have some
examples of dynamic and controls-based charting techniques he

http://www.geocities.com/jonpeltier/.../Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

arno wrote:

Hi Keith,


When I click on one of the the buttons I need to update
all six graphs and change their data ranges to point to
different rows on the worksheet containing the data. I


I usually use named ranges for the graphs. instead of changing the
properties of the graph i manipulate the definition of the named range.
usually, this works with formulas (no macros!) and the =OFFSET() function to
define the named range. So, you have to know where the starting point of
your data is and how many rows (or columns) you have.

eg: you have a spinbutton that gives you numbers 1, 2, 3, 4.. to cell
"myrange", in cell "nrRows" you have the number of rows that changes
according to "myrange". (you have a table that has 1,2,3 etc and a
count-function to count your records and you use vlookup. you could have the
nr. of records at the top of your data and you use another offset-function
to find the correct value). your data is in colums A through D, with the
spinbutton you define which colum it is. the definition of range "myYData"
would be: = OFFSET(A1, 0, myRange-1, nrRows, 1). in your chart you define
the y-range as =sheet1!myYdata (make a reference to the workbook or sheet, I
do not remember now, the name alone will not work).

best regards

arno