Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

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,COUNTA(Data!$F:$F)+1,COUNTA(Data!$2:$2)+1)

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
0
bvanscoy678
Asked:
bvanscoy678
  • 2
1 Solution
 
Rory ArchibaldCommented:
You do need to set up a named range for each series.
0
 
bvanscoy678Author Commented:
Okay, I'll give that a try. Thanks
0
 
bvanscoy678Author Commented:
I think I have it figured  out with some help from chandoo.org

X Axis      =OFFSET(Data!$F$3,,,COUNT(Data!$F:$F))
Score      =OFFSET(Data!$G$3,,,ROWS(X_axis))
Average      =OFFSET(Data!$H$3,,,ROWS(X_axis))

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now