Dynamic Chart Source Data
Hello,
I am pretty close, but I have something messed up in my name range, so my chart is wrong. My Data is listed in a worksheet, while my chart will be part of a mini dashboard on another worksheet. The top chart (chart!) is my attempt to create a dynamic source for the chart. The bottom chart is a simple example of what I am trying to do.
my name range:
=OFFSET(Data!$F$2,0,0,COUN
Then I set my chart data source to:
=data!ChartTest
But, I am still getting the formulas listed in my chart.
I am not certain if I need to create an offset for each series (test score, average), then assign each series as the chart source data. In my reading, I have seen it both ways. The examples are always a bit different, so hard to know exactly what fits.
**After more reading, I think I might need to set up a name range for the data labels and each series (score and average). I can see my formula above is looking for both the column and the rows, but I found many more examples on how to do it separately. Ideally, I would like to learn how to combine the function, so I can be more versatile in my approach. I totally get what I am trying to do, but just missing something to make it click.
Thanks,
Brent
EE-dyanmic-chart-range.xlsm
I am pretty close, but I have something messed up in my name range, so my chart is wrong. My Data is listed in a worksheet, while my chart will be part of a mini dashboard on another worksheet. The top chart (chart!) is my attempt to create a dynamic source for the chart. The bottom chart is a simple example of what I am trying to do.
my name range:
=OFFSET(Data!$F$2,0,0,COUN
Then I set my chart data source to:
=data!ChartTest
But, I am still getting the formulas listed in my chart.
I am not certain if I need to create an offset for each series (test score, average), then assign each series as the chart source data. In my reading, I have seen it both ways. The examples are always a bit different, so hard to know exactly what fits.
**After more reading, I think I might need to set up a name range for the data labels and each series (score and average). I can see my formula above is looking for both the column and the rows, but I found many more examples on how to do it separately. Ideally, I would like to learn how to combine the function, so I can be more versatile in my approach. I totally get what I am trying to do, but just missing something to make it click.
Thanks,
Brent
EE-dyanmic-chart-range.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I have it figured out with some help from chandoo.org
X Axis =OFFSET(Data!$F$3,,,COUNT(
Score =OFFSET(Data!$G$3,,,ROWS(X
Average =OFFSET(Data!$H$3,,,ROWS(X
The name range is created for the labels in the X-Axis. Then my Score and Average series counts the rows in my X_Axis range with values. My biggest confusion had to do with fact I did not comprehend that each column needed a name range. I thought I only needed to create one name range that would dynamically expand for the columns and the rows. I kept reading the syntax for offset and countA with the confusion of the parameters of height and width just messed me up.
thanks
X Axis =OFFSET(Data!$F$3,,,COUNT(
Score =OFFSET(Data!$G$3,,,ROWS(X
Average =OFFSET(Data!$H$3,,,ROWS(X
The name range is created for the labels in the X-Axis. Then my Score and Average series counts the rows in my X_Axis range with values. My biggest confusion had to do with fact I did not comprehend that each column needed a name range. I thought I only needed to create one name range that would dynamically expand for the columns and the rows. I kept reading the syntax for offset and countA with the confusion of the parameters of height and width just messed me up.
thanks
ASKER