Link to home
Start Free TrialLog in
Avatar of Mark Wood
Mark WoodFlag for United States of America

asked on

Excel Formula Help

I have a spreadsheet where i am wanting to track blood pressure. On sheet 1 there is a chart for the blood pressure where it pulls data from the blood pressure sheet. What i am wanting to do is use the Start Date and End Date above the chart to only pull the data from the blood pressure sheet between those dates to populate the chart.

i have added a sample spreadsheet for review.Health and Fitness Tracker-v1a.xlsx
Avatar of byundt
byundt
Flag of United States of America image

The simplest way is to put your data in a Table (using the Insert...Table ribbon item). You may then filter the Table by date and the chart (which starts by including all your data) will automatically display only the visible rows.
Avatar of Mark Wood

ASKER

Yes that will work but it makes it so i am always filtering the table. When you are collecting the blood pressure data twice a day for a year that could be a pain.

Couldn't it be done with some sort of vlookup or something?
I could also do it with dynamic named ranges, or with a formula that copies a subset of your data over to a new range of cells that actually gets plotted.

If you have Microsoft 365 subscription, I could use the FILTER function for this latter purpose.
Another option (if you are familiar with Power Query), is to use something like the attached.  Changing the DateRange, and then right-clicking the Result will update the Chart.

EE.xlsx
I created named ranges for StartDate, EndDate, PlotDates, PlotSystolic, PlotDiastolic, PlotHeartRate. I could then change the series to use 'Blood Pressure'!PlotDates for the range of dates.

Health-and-Fitness-Tracker-v1a.xlsx
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please let me know if you want me to delete the sample file from my post.
It is easy if you are willing to use vba.  The filtering is automatically applied whenever you change the start date or end date in the dashboard,
filter blood pressure by date.xlsm

Option Explicit

Private Sub Worksheet_Activate()
    Call filterBloodPressure
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [k5,m5]) Is Nothing Then Call filterBloodPressure
End Sub

Sub filterBloodPressure()
    Sheets("Blood Pressure").ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
        ">=" & [k5], Operator:=xlAnd, Criteria2:="<=" & [m5]
End Sub


Open in new window

Data is already in a Table but don't need to have all the blank rows ready for data, the table will expand as required.

You can also use a Pivot Chart linked to a Pivot Table to display the data, the filter options are then on the chart rather than having to filter elsewhere.

See attached, I have reduced the rows in the Table to only those used and inserted a Pivot Table and Chart. The Pivot Table is linked to the data Table so will include all new data when added but will need a refresh.

Health-and-Fitness-Tracker-v1a.xlsx
I see two ways to realize that ...
  1. I've done a similar task with charting part of a big heap of temperature data. I've just stuffed the complete data into the diagram and just changed the X-axis limits. With real much data it gets a bit slow, but it works. Unfortunately there's no way to let the Xmin and Xmax settings of a chart be determined by pointing excel to da field, but I've managed that with some macro (this is for several charts, and works with acceptable speed on about 650.000 lines of data):
    Sub ScaleAxes()
       Dim x As Chart
       Dim xa As Axis
       Dim s As Worksheet
    
       For Each x In ActiveWorkbook.Charts
          For Each xa In x.Axes
             Select Case xa.Type
                Case xlCategory
                    xa.MinimumScaleIsAuto = False
                    xa.MaximumScaleIsAuto = False
                    xa.MaximumScale = CDbl(CDate(Sheets("Data").Range("I2").Value))
                    xa.MinimumScale = CDbl(CDate(Sheets("Data").Range("I1").Value))
                Case Else
            End Select
          Next xa
       Next x
    End Sub

    Open in new window

  2. Create a new worksheet, the use the database query feature pointing to the full data worksheet as database (maybe you need to use a second excel workbook as data miner). Use SQL or some other query mechanism to filter for anything you desire. Use the query result as data source for your chart.
Thanks for the help.