Thread: How to plot 2 values per row sequentially?

1. 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. 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)
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```

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

vanceh (2015-11-17)

4. 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. 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

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

vanceh (2015-11-18)

7. 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

Posting Permissions

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