Use a named range in a scatter chart

Question

I would like to plot data that is not consecutively written in a column in an Excel 2010 scatter chart. In order to do it I named two ranges that look like =A5,A10...A1000as name1 and name2. I had to use names as the ranges were to big to be handled by the "select data" windows, despite the amount of data is perfectly plotted if they were consecutively written in a column. Then I tried to write the defined names when selecting the data, but an error was prompted. I could avoid the error by writing ={"name1"}, which worked just for the X axis data. When I use the same syntax for y axis, it is transformed to ={0}.

I have read about many dynamic charts using the offset function, but I think this is too much for what I wanted: simple fixed named ranges.

Is there any way to assign named ranges to a scatter chart without using the offset function?

Answer 1

Very good question, I've asked this myself but never bothered to get the answer Before! For some reason, charts do not seem to take workbook global variables for input. You have to use sheet local named ranges, as I show below:

Edit: it works with named ranges of non-consecutive cells too, as the question asks for