Link the Series x & Y values to a number in a cell

**stockgoblin42** · 04-29-2013, 07:28 PM

Hi,

I have a worksheet template that I put stocks on and I'd like to make the chart accept input from a cell when determining the range of the data series.

Example:

Stock "A"
Series X Values:
=fade!$L$1:$L$12

Stock "B"
Series X Values
=fade!$L$1:$L$24

I have a cell in the worksheet that tells me how old the stock is at "L10". Can I use it to vary the Series X Values of the chart?

Series X Values
=fade!$L$1:$L$(sheet("data").Range("L10")

How would I do that with proper syntax?

**Andy Pope** · 04-30-2013, 04:10 AM

Search the formula for dynamic named ranges.

**dilipandey** · 04-30-2013, 08:42 AM

Hi stockgoblin,

Follow the suggestion give by Andy.
If stuck, upload a sample workbook.. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**stockgoblin42** · 04-30-2013, 08:59 AM

Still looking into it. Here's the current formula:

=SERIES("close",fade!$L$1:$L$9,fade!$J$1:$J$9,1)

and I want $L$9 and $J$9 - the 9 to be variable from the value in L10 on sheets "data".

I'll see if I can cut down my workbook enough to upload it.

**stockgoblin42** · 04-30-2013, 09:15 AM

getting closer but not yet:

Please Login or Register  to view this content.

**stockgoblin42** · 04-30-2013, 09:23 AM

almost

Please Login or Register  to view this content.

maybe

**dilipandey** · 04-30-2013, 09:29 AM

can not see anything as only you have the workbook

Regards,
DILIPandey
<click on below * if this helps>

**stockgoblin42** · 04-30-2013, 09:51 AM

I tried but I'm stuck, here's the workbook...

looking for chart series to be a dynamic named range of columns L & J.

thanks for any help

**stockgoblin42** · 04-30-2013, 11:10 AM

Alright! I got it! Wow, that's weird defining the ranges first and then referring to them. It'll be useful in the future I'm sure. I'm glad I learned how to do it. Still a bit convoluted but surely enjoyable for all to delight in for years to come! Ha!