Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic cell ranges in charts | Charts and Charting in Excel | |||
Dynamic charts without Named Ranges? | Charts and Charting in Excel | |||
Using named ranges in dynamic charts (excel 2007) | Excel Worksheet Functions | |||
of Named Ranges, Dynamic Charts and scroll bars... | Charts and Charting in Excel | |||
a question regarding dynamic ranges and charts | Charts and Charting in Excel |