Excel Graph Series based on cell value

Hello Experts,

I am trying to create a graph in which the series length is based on the value a user types into a cell.
For instance, if the user type in a "2" then the series length will show the first two values in the series.  If the user types in a "4" then the series length will show the first four values in the series.  The minimum value would be 1 and the maximum value would be 13.

Thanks in advance.

JJ

Any suggestions on how that can be done?
JJINFMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
The attached file uses two dynamic Names, ChartLabels and ChartValues, to define the data source.  As you update the value in E1, the ranges automatically resize.
Q-25734326.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JJINFMAuthor Commented:
This seems is what I am looing for, but how does it work?
0
CSLARSENCommented:
Hi
Please find enclosed two chart suggestions which operate dynamically on a cell value.
hoping to be helpful
cslarsen
Book1.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

CSLARSENCommented:
Sorry for the crosspost, I did not realise it took me that long.....
cheers
cslarsen
0
JJINFMAuthor Commented:
OK, I see how in book1 the #NA prevents any value from being shown for that period.  If the if/then function works well.

Thanks
0
Patrick MatthewsCommented:
JJINFM,

With respect, CSLARSEN and I used virtually identical methods to do this, and my post had the virtue of being first.  I would have been pleased with a split, but I feel the present outcome is unfair.

Patrick
0
JJINFMAuthor Commented:
Matthwspatrick,

Honestly, I could not understand how your method works which is why I asked in comment ID: 29941854.  
With CSLARSEN, I could see the use of the #NA in the IF function.

Your solution does work, but it doesn't help me much if I don't see the method being used to arrive at the solution.

JJ
0
CSLARSENCommented:
JJ,
Please feel free to re-assign points to Patrick also.
cheers
cslarsen
0
Patrick MatthewsCommented:
JJ,

I made my post at 04/06/10 03:11 PM (all times EDT), shortly before stepping away from my computer for a short break.

You replied asking for more info at 04/06/10 03:25 PM

You closed the question at 04/06/10 03:44 PM.

In other words, you gave me only 33 minutes to reply.  What was the rush?

As for how my suggestion works:

1) I created a dynamic Name, ChartLabels, referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$8,Sheet1!$E$1,1)

I also created ChartValues, similar to the above but changing references for Col A to Col B

2) For the chart, I defined the Values to use    =Q_25734326.xls!ChartValues
and the Labels to use      =Q_25734326.xls!ChartLabels

(I had originally entered those as =Sheet1!ChartValues and =Sheet1!ChartLabels; after saving the workbook Excel automatically changed the reference from the sheet name to the Workbook name)


This web page provides a good introduction to dynamic Names, although it uses the OFFSET function instead of the INDEX function: http://www.contextures.com/xlNames01.html#Dynamic

Patrick
0
Patrick MatthewsCommented:
cslarsen,

Thanks for that.  None of my crankiness was directed toward you :)

Patrick
0
JJINFMAuthor Commented:
Patrick,

I just your crankiness was directed just towards me...

I guess when I look for a soltion, I expect to get an explanation, not just quick example.

I would be more than happy to discuss in whatever forum the experts have to resolve this mater.

JJ
0
Patrick MatthewsCommented:
JJ,

I have no problem whatsoever with explaining my methods.  Indeed, I posted a pretty full explanation above.  What I did take issue with was your giving me a scant 33 minutes to reply to your request for more info before simply closing the question.

Like all of the Experts here, I am a volunteer.  All I ask is that you allow me a reasonable amount of time to reply before throwing in the proverbial towel.  By your own admission, you had two working solutions, so your haste in closing this was unseemly.

Patrick
0
Patrick MatthewsCommented:
I have already posted a Community Support request to ask a Moderator to review.
0
JJINFMAuthor Commented:
Good, I agree with a moderator review.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.