Creating dynamic chart showing last 90 calendar days entries

[Ironman]

Hello

I have literally rubbed my eyebrows away trying desperately to create a dynamic line chart with columns A, G and H from the below data, so the chart shows only the most recent 90 calendar days entries (NOT the last 90 entries - please let me know if it's necessary to insert a daily entry with a zero value for this to work as I would like).

I've looked in the forum and on the net and I just don't understand if I am able to do this directly from this data or if I have to convert the below to a table with just 3 columns and work from that. However, if I create a separate table I cannot understand how the data would copy from the data sheet below to the table.

After many very late hours trying to work it out it myself I've been on the verge of just giving up and I would be so grateful for a solution that will make this work as it's my exercise bike fitness tracker!

Thank you so much!

Book1
	A	B	C	D	E	F	G	H
10	DATE	TIME (h:mm)	DIST. (km)	DIST. (mls)	SPEED (mph)	LEVEL	HR BPM	Power (W)
11	Sat, 6 Jan 2018	0:33	15.2	9.4	17.2
12	Wed, 10 Jan 2018	0:43	21.2	13.2	18.4
13	Tue, 16 Jan 2018	0:48	21.2	13.2	16.5
14	Fri, 19 Jan 2018	0:52	22.2	13.8	15.9
15	Tue, 23 Jan 2018	0:53	26.3	16.3	18.5		>100
16	Thu, 25 Jan 2018	0:46	21.3	13.2	17.3
17	Sat, 27 Jan 2018	0:45	20.0	12.4	16.6
18	Sun, 28 Jan 2018	0:46	20.7	12.9	16.8
19	Wed, 31 Jan 2018	0:50	23.6	14.7	17.6		122
20	Wed, 14 Feb 2018	0:45	21.0	13.0	17.4
21	Thu, 22 Feb 2018	0:45	20.9	13.0	17.3		<120
22	Sun, 4 Mar 2018	0:45	21.0	13.0	17.4		<110
23	Tue, 6 Mar 2018	0:48	22.0	13.7	17.1		111
24	Sat, 10 Mar 2018	0:47	20.8	12.9	16.5
25	Sat, 17 Mar 2018	1:01	25.8	16.0	15.8		110
26	Thu, 12 Apr 2018	0:45	19.3	12.0	16.0		110
27	Mon, 23 Jul 2018	0:30	11.8	7.3	14.4		110	90
28	Sat, 11 Aug 2018	0:30	13.4	8.3	16.6		120
29	Tue, 25 Sep 2018	0:30	12.4	7.7	15.4	5 (42%)	108	100
30	Wed, 26 Sep 2018	0:40	14.6	9.1	13.6	5 (42%)	107	95
31	Wed, 3 Oct 2018	0:30	12.0	7.5	14.9	5 (42%)	113	100
32	Mon, 8 Oct 2018	0:30	10.9	6.8	13.5	5 (42%)	103	91
33	Sun, 14 Oct 2018	0:40	15.2	9.4	14.2	5 (42%)	107	95
34	Thu, 18 Oct 2018	0:35	12.8	7.9	13.6	5 (42%)	120	91
35	Sun, 21 Oct 2018	1:00	23.2	14.4	14.4	5 (42%)	118	97
36	Wed, 24 Oct 2018	0:35	12.2	7.6	13.0	5 (42%)	95	87
37	Sat, 27 Oct 2018	0:45	18.0	11.2	14.9	5 (42%)	104	99
38	Tue, 30 Oct 2018	1:00	24.5	15.2	15.2	5 (42%)	111	102
39	Fri, 2 Nov 2018	1:00	24.1	15.0	15.0	5 (42%)	106	100
40	Mon, 5 Nov 2018	1:00	24.8	15.4	15.4	5 (42%)	105	103
41	Wed, 7 Nov 2018	0:45	19.1	11.9	15.8	5 (42%)	105	106
42	Fri, 9 Nov 2018	1:00	25.2	15.6	15.6	5 (42%)	115	102
43	Sun, 11 Nov 2018	1:00	26.9	16.7	16.7	5 (42%)	111	112
44	Wed, 14 Nov 2018	1:00	26.4	16.4	16.4	5 (42%)	110	111
45	Mon, 19 Nov 2018	0:45	18.7	11.6	15.5	5 (42%)	103	104
46	Wed, 21 Nov 2018	1:00	26.2	16.3	16.3	5 (42%)	108	109
47	Fri, 23 Nov 2018	1:00	22.8	14.2	14.2	6 (50%)	105	117
48	Sun, 25 Nov 2018	1:00	23.1	14.3	14.3	6 (50%)	108	118
49	Wed, 28 Nov 2018	1:02	23.9	14.8	14.4	6 (50%)	105	118
50	Sat, 1 Dec 2018	1:00	23.6	14.7	14.7	6 (50%)	107	121
51	Tue, 4 Dec 2018	1:00	23.8	14.8	14.8	6 (50%)	109	122
52	Thu, 6 Dec 2018	1:00	22.7	14.1	14.1	6 (50%)	110	117
53	Sat, 8 Dec 2018	1:00	23.4	14.5	14.5	6 (50%)	107	121
54	Mon, 10 Dec 2018	1:00	25.6	15.9	15.9	6 (50%)	131	131
55	Wed, 12 Dec 2018	1:00	24.2	15.0	15.0	6 (50%)	110	124
56	Fri, 14 Dec 2018	1:00	23.3	14.5	14.5	6 (50%)	104	120
57	Mon, 17 Dec 2018	1:00	25.3	15.7	15.7	6 (50%)	108	130
58	Wed, 19 Dec 2018	1:00	24.4	15.2	15.2	6 (50%)	108	125
59	Fri, 21 Dec 2018	1:00	21.5	13.4	13.4	6 (50%)	90	110
60	Sun, 23 Dec 2018	1:00	24.0	14.9	14.9	6 (50%)	99	123
61	Wed, 26 Dec 2018	1:00	23.7	14.7	14.7	6 (50%)	94	122
62	Fri, 28 Dec 2018	1:00	24.9	15.5	15.5	6 (50%)	102	128
63	Sun, 30 Dec 2018	1:00	23.1	14.3	14.3	6 (50%)	97	119
64	Wed, 2 Jan 2019	1:00	21.3	13.2	13.2	6 (50%)	90	109
65	Fri, 4 Jan 2019	1:00	23.6	14.7	14.7	6 (50%)	95	121
66	Sun, 6 Jan 2019	1:00	24.9	15.5	15.5	6 (50%)	109	128
67	Tue, 8 Jan 2019	1:00	23.0	14.3	14.3	6 (50%)	89	119
68	Thu, 10 Jan 2019	1:00	26.7	16.6	16.6	6 (50%)	110	137
69	Sat, 12 Jan 2019	1:00	23.5	14.6	14.6	6 (50%)	92	121
70	Mon, 14 Jan 2019	1:00	25.0	15.5	15.5	6 (50%)	101	128
Exercise Bike

 

[RasGhul]

Re: Help with creating dynamic chart showing last 90 calendar days entries please?

Excellent good to hear. Its better if pivot tables have there own sheet anyway.

I was looking for a formula to create dynamic named ranges for your date/hr/power columns so that you wouldn't need to update your array formulas.

If I find it I'll update the last file.

 

[Ironman]

Re: Help with creating dynamic chart showing last 90 calendar days entries please?

Hi Rasghul - I'm not sure it's necessary for you to go to that trouble (unless it makes it more efficient?) because everything updates automatically now.

Edit - I've just checked the chart again and it doesn't remove the earliest entry!

 

[Ironman]

Re: Help with creating dynamic chart showing last 90 calendar days entries please?

Hi Rasghul - In case you didn't read my edited reply, I've just checked the chart again and it doesn't remove the earliest entry so I guess it's not dynamic any more?

 

[RasGhul]

Re: Help with creating dynamic chart showing last 90 calendar days entries please?

Correct as the table looked after that

 

[Ironman]

Re: Help with creating dynamic chart showing last 90 calendar days entries please?

I was looking for a formula to create dynamic named ranges for your date/hr/power columns so that you wouldn't need to update your array formulas.

If I find it I'll update the last file.

Hi Rasghul - if you have a look at the Last90R Data sheet that refers to the dynamic Last 90 Runs chart there are Offset formulas and named ranges in there that I didn't create that might help you...

 

[RasGhul]

Converted Table to normal range
Cleaned up formula to work with new range

Note the 90 Days array formula updates instantly but you will now need to revert to the refresh-all button or the vba solution from the previous post


https://www.dropbox.com/s/pek61vigs27rad0/Exercise Log For RasGhul_220119.xlsm?dl=0

 

[Ironman]

Hi Rasghul, thanks for doing that.

Unfortunately although the formula updates instantly, the earliest entry still doesn't drop out of the table/chart.

The code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub

was already in the Exercise Bike sheet so I didn't need to add it.

I also clicked Refresh All and it made no difference.

 

[RasGhul]

The formula is calculating correctly;

Criteria Today() - 90 = 25-10-18
There are still 37 Dates from 25-10-18 to today that meet the criteria
It wasn't until I reduced the days criteria to 87 (28-10-18) and refresh did the oldest date change.

The oldest date won't change until Friday 26th Jan.

 

[Ironman]

Agh! Apologies for that - it's what happens when I get 2 hours' sleep

Many thanks once again for all your help and persistence RasGhul.