How to reference the Nth column in a named range in Excel for plotting?

user_2704213 0
Viewed 810

I have a table with, say, 3 rows and 3 columns, the first one being the date and the other two being random data such as below (data start in A1):

Date Series1 Series2 Jan 1 10

I have created the first named range as 'Date' for A2:A4 and the second named rage as 'Data' for B2:C4.

I wish to plot Series1 using the named range. Of course, I could simply plot Series1 manually with a fixed range, but that does not fit what I need to do.

Most of the documentation I found either refers to single-column named range or does not address the issue of plotting. The closest reply to my question is found here but Excel returns an error when I insert my formula for the plot. Excel returns "This function is incorrect" when I insert =INDEX(Sheet1!Data,,1) in the series values, though this works outside of the charting environment.

My question is therefore: can I use a named range with more than one column when charting and, if so, how do I reference the Nth column?

EDIT: In my broader use case, the range is dynamic and is defined as =OFFSET($B$1,0,0,COUNT($B:$B)-1). Any solution for plotting should therefore remain purely dynamic.

named-ranges charts plot excel
1 Answers

As you've discovered, you can't use INDEX() in the chart range properties. There is a bit of a way round what you want though. If you use the same formula =INDEX(Sheet1!Data,,1) in an empty column, then you can then chart that range instead.

You can even then link the column number to another cell, making it dynamic:

enter image description here

You can also do a similar thing with the series name, although it would perhaps be easier just to extend the data named range to include the header.

user_78584510
Related