Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add vertical lines to charts (XP)

    Say you have a bunch of Line or Line - 2 axis charts plotting blood parameters on the Y-axes vs. date on the X-axis. I presently add short vertical colored lines using "autoshapes" to indicate dates of, say a specific drug injection. This works OK until the end of the year comes and we want to extend the X-axis for another year. All of the data moves to the left, but the autoshape vertical lines must all be manually moved. (the Autoshape "size with chart" doesn't affect this...).

    How can I insert these lines as "data" so they will automatically be shifted when the X-axis is changed?

    TIA

    ash

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add vertical lines to charts (XP)

    Sounds like a good challenge! Would you make up a dummy chart so we can see what you want it to look like and then attach it to a reply? Thanks! PS, are we allow to use VBA? Also, we will have to have the information (dates and bar) that you use to create the vertical lines as entries in the spreatsheet. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add vertical lines to charts (XP)

    Best way is to use a XY chart instead of a line.

    You can create a new data set that JUST has the lines to draw and add datalabels for any "note". Since the line has x,y coords, as you change the axis, the line stays at its coords.

    Steve

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add vertical lines to charts (XP)

    Steve (below) punched me in the right place right away!!! The data is, and has to remain, XY since the X-axis intervals can not be considered constant.

    I've cobbled up a worksheet of real data and a current chart. Note that in addition to highlighting the "injection" data (in this case, Procrit) with short vertical lines, we also like to block off "Cycles" with other vertical lines and TextBox labels.

    Edit: I noticed that if I just clicked on the attachment it opened in Qickview Plus and doesn't look like the orginal at all. Saving and the openin in Excel was OK - guess you guro's already know that
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add vertical lines to charts (XP)

    Most is possible (without macro) as I suggested

    Couple questions, before I work up an example:
    The orange lines, occur on a certain date (x), what is the min and max Y value wanted? Do you want the bottom to be all the same spot and the max to be on the hemoglobin line? or some other variant?

    5-days Prednisone "boxes". I don't see any data for where this should go. Is the width constant or does it have a min-max date range? What is the box ht, constant or what. What is the Y-value for the placement?

    "Normal range" label. Should this stay in a constant graph position (changing axes will not affect) [use a textbox] OR is there a certain XY spot that you want it?

    What are the dates of the "cycles" is there a pattern or entered manually?
    What are the date(s) of the "rest period" is there a pattern or entered manually?

    One other question I thought of: You have your data setup horizontally across columns. I have found it easier to manipulate (my preference) if the data is setup down the rows: more fits on the page so there is less scrolling for the user and it prints out easier. Also columns widths can be adjusted for each "type" of data instead of requiring all columns to match since all of types are in each column. Does it matter which direction your data is in? If you want it horiz I won't change it, if it doesn't matter I will play when I play with making the chart.

    With this info, I think I could make a chart the way you want it. You probably will need an additional sheet (could be hidden) to do some of the "chart-magic"

    Steve

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add vertical lines to charts (XP)

    Couple questions, before I work up an example:
    The orange lines, occur on a certain date (x), what is the min and max Y value wanted? Do you want the bottom to be all the same spot and the max to be on the hemoglobin line? or some other variant?
    ans: First, notice when date (x) occurs w/o other data

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add vertical lines to charts (XP)

    I think this pretty much does what you want.

    You might want to use some dynaic rangenames if your data is going to grow alot

    I have a comment sheet with some descriptions
    Chart sheet with the chart change; change the X and Y axis, the lines stay where they should Only the text boxes remain (Normal range box, 5-day pred lable, and 40,000 procrit label) to be manually moved, though they could be linked also if desired. The Cycle names and "rest period" name, cycle lines, pred lines and procrit lines will move (they are locked on particular dates)

    Add Lines sheet has the data for the lines
    Data has the data (transposed from your data with a couple modifications

    Your sheet is still atttached for reference, but I did not use it for anything so it may be deleted without affecting the workbook.

    Steve
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jan 2002
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add vertical lines to charts (XP)

    Steve-
    You are a Chartsman Extraordinaire.

    I encourage others to review your neat tricks regarding the linking of charting frills to the x-axis so that they will shift as the scale is extended. This is good stuff [img]/forums/images/smilies/smile.gif[/img]

    I do have one more mundane question:
    I noticed on your sample Chart that you formatted the x-axis numbers as: mm/dd;@. What's the ";@" all about???

    Thanks again for a wonderful piece of work.

    ash

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add vertical lines to charts (XP)

    Thanks for the kind words.
    About the number format: I actually just picked one and I guess the" ;@" came along for the ride as being with the one I chose. It shouldn't be needed and can be deleted. If you have a number format in the text part, the @ is needed to put in the text that you entered into the cell to display it.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •