JJINFM
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry for the crosspost, I did not realise it took me that long.....
cheers
cslarsen
cheers
cslarsen
ASKER
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
Thanks
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
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
ASKER
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
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
JJ,
Please feel free to re-assign points to Patrick also.
cheers
cslarsen
Please feel free to re-assign points to Patrick also.
cheers
cslarsen
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!
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!ChartValue
and the Labels to use =Q_25734326.xls!ChartLabel
(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
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!
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!ChartValue
and the Labels to use =Q_25734326.xls!ChartLabel
(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
cslarsen,
Thanks for that. None of my crankiness was directed toward you :)
Patrick
Thanks for that. None of my crankiness was directed toward you :)
Patrick
ASKER
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
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
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
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
I have already posted a Community Support request to ask a Moderator to review.
ASKER
Good, I agree with a moderator review.
ASKER