2

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.

user89073
  • 317
  • 1
  • 3
  • 11

1 Answers1

0

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.

Joe
  • 616
  • 2
  • 12
  • 27
  • Thank you for taking the time. Your answer does indeed solve one of my questions. However, my use case is a bit more complicated than this and my objectif to provide a minimal working example may have led you astray. My objective is to have a dynamic named range. If I understand correctly your solution, this would not prevent me from having to manually update the graph. Even having =INDEX(Sheet1:Data;row()>(counta(a:a)-1);I1) would not help as it would fill cells beyond row 5 regardless. I edited my question to take this special case into account. – user89073 Mar 29 '21 at 14:59