Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Texas, USA
    Posts
    32
    Thanks
    13
    Thanked 2 Times in 1 Post

    How to plot 2 values per row sequentially?

    Using Excel 2013 (which I'm not very familiar with yet), I've been trying to put together a specific kind of chart for the following kind of data set.

    The data are blood pressure measurements taken in the morning and evening of each day; the data for each month of the year are on a separate worksheet. I need to put the morning and evening values for each day on the same row for ease of entry (by the person I'm making the spreadsheet for) and for the printed worksheet for the month to fit legibly on one page. Row 1 contains the column headers. For each of the remaining 28 to 31 rows (depending on which month it is):

    Column A has the date.
    Column B has the top number (systolic) of the morning blood pressure.
    Column C has the bottom number (diastolic) of the morning blood pressure.
    Column D has the top number of the evening blood pressure.
    Column E has the bottom number of the evening blood pressure.

    What I want to do on each month's worksheet is to plot a line graph so that for one series, B2 is followed by D2, followed by B3, followed by D3, followed by B4, followed by D4, etc., all on the same series line (i.e., all the top blood pressure numbers in chronological sequence on one line). For a second separate series line, I want C2 followed by E2, followed by C3, followed by E3, followed by C4, followed by E4, and so forth (i.e., all the bottom blood pressure numbers in chronological sequence on a second line). I haven't been able to figure out how to get the chart to do this, and I haven't found any solution online (probably because I don't know the terminology well enough to search properly). Any help would be much appreciated.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Vanceh,

    The code in the background will build a linear table in a hidden sheet the will automatically update the graph as you enter data

    V1.png

    In the Worksheet module of the sheet you enter data
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateStats
    End Sub

    In a standard module
    Code:
    Public Sub UpdateStats()
    '--------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet, row As Integer
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets("Stats")
    row = 2
    '--------------------
    'BUILD LINEAR TABLE
    ws2.Cells.ClearContents
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    For I = 3 To LastRow
        ws2.Cells(row, 1) = ws1.Cells(I, 1)
        ws2.Cells(row, 2) = ws1.Cells(I, 2)
        ws2.Cells(row, 3) = ws1.Cells(I, 3)
        row = row + 1
        ws2.Cells(row, 1) = ws1.Cells(I, 1) + 0.5
        ws2.Cells(row, 2) = ws1.Cells(I, 4)
        ws2.Cells(row, 3) = ws1.Cells(I, 5)
        row = row + 1
    Next I
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    vanceh (2015-11-17)

  4. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Texas, USA
    Posts
    32
    Thanks
    13
    Thanked 2 Times in 1 Post
    Maudibe--

    Wow, thanks! This does exactly what I want. I do have a couple of questions because I'm not that experienced with VBA (I've used it a bit in Word but not Excel). Since I want the workbook to have a separate sheet for each of the 12 months, do I need to add a new hidden sheet to accompany each month's (visible) sheet? Do I add the Worksheet_Change code to each month's sheet? If so, do I do that by calling up the VBA Editor while the given month's sheet is the active one, and then just pasting in the same code?

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Venceh,

    You will only need one hidden Stats sheet but the linear mapping of the date, systolic, and diastolic data for each month can be placed in adjacent columns. You are correct that each sheet must contain the Worksheet_Change event routine but with a slight modification. Each Worksheet_Change event fires the UpdateStats routine but passes the number of the column where that sheets data is stored.

    Simply navigate to any month sheet and start entering data. Watch the graph update as you go along. Entering text instead of a number will be equivalent of adding a zero so you will see a rapid drop in the line for that series.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-11-18 at 07:35.

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    vanceh (2015-11-18)

  7. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Texas, USA
    Posts
    32
    Thanks
    13
    Thanked 2 Times in 1 Post
    Maud--

    Many thanks! You've not only helped me with this particular problem but also taught me new things about Excel that I can use in future work.

    Best wishes,
    Vance

Tags for this Thread

Posting Permissions

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