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

 

[Ironman]

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

That's perfect!

Thanks EVER so much RasGhul, you've made my week - now I can go to bed (it's 4am in the UK )

 

[RasGhul]

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

You're welcome Ironman thanks for the feedback.

 

[Ironman]

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

Hi again RasGhul - don't worry, your chart's still working beautifully!

If you recall, I was no longer able to use the Custom View function which we couldn't resolve. It seems it's because the Exercise Bike sheet is now a table.

I posted a question about this yesterday https://www.mrexcel.com/forum/excel...yed-out-every-sheet-workbook.html#post5210991 and received a reply but I'm nervous about following the suggested solution as I'm afraid of messing things up.

Would you be able to help me please?

Many thanks!

 

[RasGhul]

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

Thats ok

You can right click anywhere in the table and choose table/ convert to range this should convert the array formula back to normal ranges but you should extend them out to 1000 rows etc.
Caution though you should test this on a copy of your working file first.

 

[Ironman]

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

Hi RasGhul, many thanks for that.

I've dragged the handle in Exercise Bike sheet down to the row I want. I tested it with an entry and it still updates fine. I then went to Last 90 Bike pivot sheet and I was unable to locate Table or "Convert to Range" or anything similar in any of the right click or ribbon options. I've also looked online but without any luck...

 

[RasGhul]

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

Can you send me the link for the current file your using and I'll look at it.

 

[Ironman]

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

It's here: https://www.dropbox.com/s/1afb3hxxesheo4c/Exercise Log For RasGhul.xlsm?dl=0

Thanks again.

 

[RasGhul]

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

Ironman,

I read the proposed solution from your other thread and decided to give the Pivot table its own sheet and included the formula elements for the array criteria. I've updated the array formula to look at the new sheet.

The pivot table defines the column data(Date HR & Power) for your Bike chart otherwise we would have to start from scratch if you wanted to build the chart without the pivot table.

Hope this helps

Note that I added today's date to the file name to protect the file you sent me;


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

 

[Ironman]

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

Thanks again RasGhul - however, Custom Views is still greyed out on all sheets

 

[Ironman]

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

Hi Rasghul, I've just re-read my comment below and I've just realised why I didn't see the option! I guess I was overtired!

I then went to Last 90 Bike pivot sheet and I was unable to locate Table or "Convert to Range" or anything similar in any of the right click or ribbon options.

I've just right clicked on the Exercise Bike table (NOT Last 90 Bike sheet) and followed your suggestion and the Custom Views works perfectly again now!

Thanks ever so much Rasghul - and I'm sorry you had to unnecessarily redo the pivot table.

Best regards