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?

Yeah that kept appearing when I saved as the new file name before the merge, there must be some object references library feature on your original sheet that isn't enabled on my excel.

All I can suggest is that you will need to build those 3 sheets into your original file, using my file as reference.

Sorry it has something to do with sndPlaysound32(wavefile, SND_ASYNC Or SND_FILENAME) and other VBA code lines that doesn't agree with my excel version.


When you enter the the array formulas into each column for the Last 90 Tab, have the formula ready in the formula bar and HOLD CTRL+SHIFT then press Enter. When successful the formula will have the curly brackets either end {Formula} which indicates its an array. Then drag the formula down 91 rows like my mock up sheet.

 

[Ironman]

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

Hi RasGhul, I found it too difficult to follow your suggestion I'm afraid, so I'm using your workbook instead.

However, I have just added a new entry to Exercise Bike and it hasn't updated the pivot table/chart even when I refreshed.

The latest workbook is here https://www.dropbox.com/s/d0a7aj233gn35wp/Exercise Log.xlsm?dl=0

I noted that the small pivot table icon in the Exercise Bike sheet (that looks like a bottom right hand corner) hasn't moved from J70 down to the latest row.

I hope renaming the chart didn't cause a problem.

Also, which version of Excel do you have?

I hope you can look at this for me please?

Many thanks again!

 

[RasGhul]

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

Hi Mate,

I've re-made the table and tested adding to rows of data and it updated properly after refresh.

Note that the Pivot table values are filtered to only show values greater than zero so we avoid having a blank row in the pivot table and chart.

https://www.dropbox.com/s/b14x46jy4x94grk/Exercise Log.xlsm?dl=0

 

[Ironman]

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

Hi again, thanks for doing that.

I don't know what I'm doing incorrectly but I added a test row of data to Exercise Bike sheet, saved it, right clicked in the pivot table and clicked Refresh and it didn't update.

Here's the file with the added row

https://www.dropbox.com/s/d0a7aj233gn35wp/Exercise Log.xlsm?dl=0

 

[RasGhul]

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

Hmm ok what version of excel are you Iron?

 

[Ironman]

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

Hi, it's 2019.

I was just thinking, have a look at the hidden 90R Data sheet that relates to the Last 90 Days Runs dynamic chart - this works without the need for a pivot table but I could never figure out how to replicate it.

 

[RasGhul]

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

Well versions shouldn't be a problem can you try the settings explained in this link and test on the latest file?

https://www.youtube.com/watch?v=HdvlA8mdfWs&feature=youtu.be

 

[Ironman]

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

Thanks RasGhul, will do.

 

[Ironman]

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

Hi again, I just noticed the columns in Exercise Bike tab were too wide for the screen so I missed the bottom right hand corner handle in cell K71 - that column is headed Column 1 - is that the way it should be?

When I entered a new row of data the handle dropped and auto updated - brilliant!

So do I still need to manually refresh the pivot or is there a way for this to be done automatically?

 

[RasGhul]

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

You can resize your table by clicking "Exercise Bike A12" above the command ribbon Table Design" will now be available after the Help tab.

Click Design
Left side of Ribbon there is Table Name and Resize Table.
Click Resize Table and a prompt will appear with your Table1 range =$A$10:$K$71
Change to =$A$10:$J$71 (or match same number of rows as your current sheet) then Ok.

You can do the following to update your Pivot but I find it annoying when building sheets but you may find it ok when only entering single rows of data;

Right Click on the Exercise Bike Tab
Click "View Code" - this will open the VBA Editor specifically for this sheet.
On the Left top drop down it currently says General - choose Worksheet
This will automatically appear at the bottom of your Exercise Log - Sheet14 VBA Window "Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub"

Add the following to this code;

thisworkbook.RefreshAll

The end result should look like this;

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

Save - then close vba window and done

**be warned as each time you make a change to your sheet it runs the refreshall function