Linking Data Labels to a Cell on Dynamically Allocated chart! Can't get it!

Hey all!

Okay, here's what's going on:

I have a line chart that is based on dynamically allocated ranges of data. It plots a measured quantity against a date. In order to allow any subset of the data to be plotted, I've created the line chart using named arrays for both series and category inputs. These named ranges are created in a worksheet using the "offset" function. This allows me to choose any start point and any end point I like, and then to plot them.

It works really well, except for one major issue: Data labels!

I need to make notes about specific data points, because they are related to process-based changes on the data I'm tracking. An example would be when we change tooling in the machine, the quantity we're measuring may change. I want to annotate that change of tooling on my chart, and have it stick with that point regardless of how I dynamically scale the data.

Right now, I have a second set of data that I call "comment" data. It is empty except for when I want to put a note, and when I do, I enter a data value just above the point I wish to annotate. This works well, and shows me that there's something going on. I then display the data labels for the "comment" data, and retype the value as the text I want.

The problem is, this is manual and kind of lame. We have a need to constantly plot different subsets of the data, and I find that when I do this, the labels I've typed in disappear, and the numbers come back.

Now, I also have a "Comment field" in the data set that drives the "comment data". I have it set up with an IF command so that only when I enter a comment does the data in the "comment Data" field generate a point (based on the data in another sheet). Thus, the only points for the "comments" data set that get plotted are those associated with comment itself.

I want to link the values in the data label (or a text box, even) wit the text in the "comment field". I have used the whole "='Sheet_name'!Cell_reference" approach, and it works fine, except that when I dynamically resize the plot area, the text in the data labels disappears!!

I'm using Excel 2010, and I can't use any third party add ins, because other departments use this file for data entry and plotting too, and they use different computers. So, what I'm wondering is this:

How can I make it so that the text in the Comment Field is always displayed in the data labels on my graph, regardless of the size of the data subset I'm plotting. If I label a point as "tool change", I'd like it to always say that, whether I plot a week, a month or even a year's worth of data.

I hope this makes sense. I've included a copy of the sheet here, too, if you want to take a look.

This is pretty frustrating to me. I think Excel 2013 can do this, but we won't be that advanced for a bit!

Thanks for any help!

Hey Andy:

I'm getting there. I see how you're getting what is going on, and I believe that I get what you're saying about the dynamic allocation of the label text based on a named range.

The problem is, that means you'll have to enter the text for each data point individually, no?

Is there a way to dynamically allocate all the data labels at once, say as an array or something? There are only 5 data points in the example, but in my application, there could theoretically be hundreds, and I need them all to label automatically and to do so based on the length of the vectors being plotted.

Is there a way to do this?

Thanks, Andy.

I have seen a bit of VB code to automate the chart labeling, but I am not good with VB at all (zero training, almost zero experience), so I was hoping that XL2010 would have some way to assign an array of value as data labels. I guess that's not going to be the case, though!

The big killer for this project isn't just that the data can grow or shrink, but that the first and last points can both change. I need some way to associate a specific point with a specific label, but I guess short of VB that there's no way to do that, is there?

It's a shame, because with the ability to dynamically scale charts and move the endpoints, it's possible to almost make a perfect chart for what I need. Only this one issue remains.

There's not some kind of array function for this, is there?

Okay, thanks.

The example only works though, I thought, when the number of data points is static. Technically, I can have anywhere between one and 1000 data points on my graph. If that's the case, then how will the labeling from your example work?

Man, that's awesome, but I must be missing something.

It works, first of all, which is awesome.

However, I don't get HOW it works. How did you get it so that each point has a specific cell assigned to it as data label text. The fact that it automatically does this for any number of points tells me I've missed something here.

I was going in and manually editing the text in each data label to be "sheet_Name!point", but it seems that yours is automatically assigning this. Can you explain to me how you do that?

Thanks, man. Sorry to be dense. I can tell I've almost got it, but this one piece of the puzzle is missing.

Thanks!