MrExcel Publishing
Your One Stop for Excel Tips & Solutions

BEN O. Follow up question PLEASEEEEEEE!!!


Posted by Kristen on December 01, 2000 5:46 AM

In reply to my post on charts and ranges your suggestion was :Instead of using the draw tool to create the lines, you could include additional data series in your charts, where all of the Y values and 8.8 for one series, and 9.5 for the other.

Once your data has been plotted, right-click on the 8.8 data series and select Format Data Series. On the Pattens tab, change the Line setting from None to Custom, and select the color red and any other settings you want. Change the Marker setting to None. Press OK and that should give you a red line across the chart at the 8.8 mark. Do the same for the 9.5 data series.


I am happy to say this worked...sort of.
what I was wondering is if there was ANY possible way to make the lines go the FULL length of the chart? It only puts a line in the middle of the chart.
any ideas???
~Kris



Posted by Ben O. on December 01, 2000 8:44 AM

Okay, here's an easy way: Right-click on your chart and select Source Data. On the Series tab, select Add. For your new series, instead of specifying an area on the spreadsheet to take the values from, you can just type the values in.

Where is says x values, type in ={0,10}

Replace 0 and 10 with the points you want your line to begin and end at.

Where is says y values, type in ={8.8,8.8}

Then you can go ahead and format the series like I told you how to before.

I hope this helps,

-Ben


Posted by Kristen on December 01, 2000 1:21 PM

Ben
Thanks for all your help BUT I have just one more little question :O)
If I do as you suggested typing in the source data manually. if the High low ranges on the spread sheet chang I need the lines to move on the chart automaiclly to reflect thoes changes, but if the source isnt using thoes specific cells it wont work like that. Sorry to be a pain :O)

: In reply to my post on charts and ranges your suggestion was :Instead of using the draw tool to create the lines, you could include additional data series in your charts, where all of the Y values and 8.8 for one series, and 9.5 for the other. : Once your data has been plotted, right-click on the 8.8 data series and select Format Data Series. On the Pattens tab, change the Line setting from None to Custom, and select the color red and any other settings you want. Change the Marker setting to None. Press OK and that should give you a red line across the chart at the 8.8 mark. Do the same for the 9.5 data series. : : I am happy to say this worked...sort of. : what I was wondering is if there was ANY possible way to make the lines go the FULL length of the chart? It only puts a line in the middle of the chart. : any ideas??? : ~Kris


Posted by Kristen on December 01, 2000 1:30 PM

Re: Let me clarify that mess! :O)

here is the senario...
I have a scatter chart
on the Y axsis I have #8-10 in.5 intervals. on the X axsis I have dates in mm/dd format.

Across the top of the spread sheet I have:
Date PH High PH low Test1 Test2
12/1 9.5 8.8 8.7 9.2

NOW...I need 2 red lines going all the way across the chart at 9.5 and 8.8 Which your suggestion did, but I need thoes source data values to point to B2 and C2 because they will be forever changing and I would like for the line to move on its own based on the data supplied in thoes 2 cells....does this make ANY Sense because I am starting to confuse mself!

Ben Thanks for all your help BUT I have just one more little question :O) If I do as you suggested typing in the source data manually. if the High low ranges on the spread sheet chang I need the lines to move on the chart automaiclly to reflect thoes changes, but if the source isnt using thoes specific cells it wont work like that. Sorry to be a pain :O) : Okay, here's an easy way: Right-click on your chart and select Source Data. On the Series tab, select Add. For your new series, instead of specifying an area on the spreadsheet to take the values from, you can just type the values in. : Where is says x values, type in ={0,10} : Replace 0 and 10 with the points you want your line to begin and end at. : Where is says y values, type in ={8.8,8.8} : Then you can go ahead and format the series like I told you how to before. : I hope this helps, : -Ben :

Posted by Ben O. on December 03, 2000 7:51 PM

Re: Let me clarify that mess! :O)

Sorry for not replying right away, Kristen, but I've been busy at work. There's an easy solution to your problem. When you type in your Y values, just reference the source cells twice (you need at least two points for a line, and you need an Y value for every X value). For your PH High you would use =(Sheet1!$B$2,Sheet1!$B$2), assuming the name of your sheet is Sheet1, and for your PH Low you would use =(Sheet1!$B$3,Sheet1!$B$3). Don't hesitate to ask if you'd like more help.
I might be a little late in my response though!

-Ben

here is the senario... I have a scatter chart on the Y axsis I have #8-10 in.5 intervals. on the X axsis I have dates in mm/dd format. Across the top of the spread sheet I have: Date PH High PH low Test1 Test2 12/1 9.5 8.8 8.7 9.2 NOW...I need 2 red lines going all the way across the chart at 9.5 and 8.8 Which your suggestion did, but I need thoes source data values to point to B2 and C2 because they will be forever changing and I would like for the line to move on its own based on the data supplied in thoes 2 cells....does this make ANY Sense because I am starting to confuse mself! : Thanks for all your help BUT I have just one more little question :O) : If I do as you suggested typing in the source data manually. if the High low ranges on the spread sheet chang I need the lines to move on the chart automaiclly to reflect thoes changes, but if the source isnt using thoes specific cells it wont work like that. Sorry to be a pain :O) :