Mark Wood
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
i have added a sample spreadsheet for review.Health and Fitness Tracker-v1a.xlsx
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.
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?
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.
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
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
Health-and-Fitness-Tracker-v1a.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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 ...
- 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
- 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.
ASKER
Thanks for the help.