HELP! Repetitively having to use new data in the same chart

[roryg90]

So my problem is that everyday I get sent a table of data which I then have to paste into an excel sheet. I repeatedly put a new table in everyday, 2 cells below the last table of data.

Then I plot the data from the 13th column of the tables into the same graph everyday to see trends.

I am having to manually do this everyday for multiple data sets.

I need a way of just pasting the data tables in everyday to below each other and the graphs just automatically updating. I have attached an example of the data I am looking at with the graph which i update.

PLEASE HELP ME THIS IS DRIVING ME INSANE.

Monitoring Analysis example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	Position Ref. (3m Centres)	8/4/20		8/4/20		8/8/20 8:00		8/8/20 8:00					8/8/20 8:00		8/8/20 8:00
5		Down Cess Rail	Down 6ft Rail	Down Main	Down Twist	Down Cess Rail	Down 6ft Rail	Measured Cant	Down Main	Twist Values	Design Cant	Diff to Design Cant	Down Cess Rail	Down 6ft Rail	Down Cess Rail	Down 6ft Rail
6				Gradient					Gradient
7				Difference					Difference
8	0 (London)	19.72	19.718	3	1000	19.722	19.72	-2 2	4	750	0	-2 2	0.002	0.002	-0.002	-0.001
9	1	19.701	19.702			19.703	19.705				0		0.002	0.003	-0.001	-0.001
10	2	19.682	19.683	0	---Level---	19.683	19.685	2	0	---Level---	0	2	0.001	0.002	0	-0.002
11	3	19.663	19.662	-2	-1500	19.664	19.662	-2	-4	-750	0	-2	0.001	0	0.001	-0.002
12	4	19.644	19.648	5	600	19.645	19.65	5	7	429	0	5	0.001	0.002	-0.001	-0.001
13	5	19.627	19.633	2	1500	19.627	19.635	8	3	1000	0	8	0	0.002	-0.001	-0.002
14	6	19.609	19.612	-3	-1000	19.609	19.614	5	-3	-1000	0	5	0	0.002	-0.001	0
15	7	19.593	19.596	0	---Level---	19.593	19.598	5	0	---Level---	0	5	0	0.002	0	-0.001
16	8	19.577	19.576	-4	-750	19.577	19.578	1	-4	-750	0	1	0	0.002	0.001	0
17	9	19.559	19.558	0	---Level---	19.559	19.56	1	0	---Level---	0	1	0	0.002	0	0.001
18	10	19.541	19.541	1	3000	19.541	19.543	2	1	3000	0	2	0	0.002	0.001	0
19	11	19.524	19.526	2	1500	19.525	19.528	3	1	3000	0	3	0.001	0.002	0.001	0.001
20	12	19.51	19.512	0	---Level---	19.510	19.514	4	1	3000	0	4	0	0.002	0.001	0.001
21	13	19.496	19.498	0	---Level---	19.496	19.499	3	-1	-3000	0	3	0	0.001	0.002	0
22	14	19.482	19.484	0	---Level---	19.482	19.485	3	0	---Level---	0	3	0	0.001	0	0.001
23	15	19.468	19.469	-1	-3000	19.468	19.469	1	-2	-1500	0	1	0	0	0.001	0.002
24	16	19.455	19.457	1	3000	19.454	19.458	4	3	1000	0	4	-0.001	0.001	0.001	0.002
25	17	19.445	19.446	-1	-3000	19.444	19.447	3	-1	-3000	0	3	-0.001	0.001	0.001	0.002
26	18	19.434	19.435	0	---Level---	19.433	19.437	4	1	3000	0	4	-0.001	0.002	0	0.002
27	19	19.421	19.424	2	1500	19.42	19.426	6	2	1500	0	6	-0.001	0.002	-0.001	0
28	20 (Country)	19.414	19.414	-3	-1000	19.414	19.415	1	-5	-600	0	1	0	0.001	-0.001	0.001
29
30													Vertical Variation To Existing		Lateral Variation To Existing
31	Position Ref. (3m Centres)	8/4/20		8/4/20		8/9/20 9:00		8/9/20 9:00					8/9/20 9:00		8/9/20 9:00
32		Down Cess Rail	Down 6ft Rail	Down Main	Down Twist	Down Cess Rail	Down 6ft Rail	Measured Cant	Down Main	Twist Values	Design Cant	Diff to Design Cant	Down Cess Rail	Down 6ft Rail	Down Cess Rail	Down 6ft Rail
33				Gradient					Gradient
34				Difference					Difference
35	0 (London)	19.72	19.718	3	1000	19.722	19.72	-2 2	4	750	0	-2 2	0.002	0.002	0	0
36	1	19.701	19.702			19.702	19.704				0		0.001	0.002	0	0
37	2	19.682	19.683	0	---Level---	19.683	19.685	2	0	---Level---	0	2	0.001	0.002	0.001	0
38	3	19.663	19.662	-2	-1500	19.663	19.662	-1	-3	-1000	0	-1	0	0	0.002	0
39	4	19.644	19.648	5	600	19.644	19.65	6	7	429	0	6	0	0.002	0.001	0.002
40	5	19.627	19.633	2	1500	19.627	19.635	8	2	1500	0	8	0	0.002	0	0
41	6	19.609	19.612	-3	-1000	19.609	19.614	5	-3	-1000	0	5	0	0.002	0	0.001
42	7	19.593	19.596	0	---Level---	19.593	19.599	6	1	3000	0	6	0	0.003	0.001	0.002
43	8	19.577	19.576	-4	-750	19.578	19.578	0	-6	-500	0	0	0.001	0.002	0.002	0.001
44	9	19.559	19.558	0	---Level---	19.560	19.559	-1	-1	-3000	0	-1	0.001	0.001	0.002	-0.001
45	10	19.541	19.541	1	3000	19.542	19.543	1	2	1500	0	1	0.001	0.002	0.001	0.002
46	11	19.524	19.526	2	1500	19.525	19.528	3	2	1500	0	3	0.001	0.002	0.001	0.002
47	12	19.51	19.512	0	---Level---	19.510	19.514	4	1	3000	0	4	0	0.002	0.002	0.001
48	13	19.496	19.498	0	---Level---	19.496	19.499	3	-1	-3000	0	3	0	0.001	0.002	0
49	14	19.482	19.484	0	---Level---	19.482	19.485	3	0	---Level---	0	3	0	0.001	0	0
50	15	19.468	19.469	-1	-3000	19.468	19.469	1	-2	-1500	0	1	0	0	0	-0.001
51	16	19.455	19.457	1	3000	19.455	19.458	3	2	1500	0	3	0	0.001	0	0
52	17	19.445	19.446	-1	-3000	19.445	19.446	1	-2	-1500	0	1	0	0	0	0
53	18	19.434	19.435	0	---Level---	19.434	19.436	2	1	3000	0	2	0	0.001	0	0
54	19	19.421	19.424	2	1500	19.42	19.424	4	2	1500	0	4	-0.001	0	0	0
55	20 (Country)	19.414	19.414	-3	-1000	19.414	19.414	0	-4	-750	0	0	0	0	0	-0.001
56
57													Vertical Variation To Existing		Lateral Variation To Existing
58	Position Ref. (3m Centres)	8/4/20		8/4/20		8/10/20 8:00		8/10/20 8:00					8/10/20 8:00		8/10/20 8:00
59		Down Cess Rail	Down 6ft Rail	Down Main	Down Twist	Down Cess Rail	Down 6ft Rail	Measured Cant	Down Main	Twist Values	Design Cant	Diff to Design Cant	Down Cess Rail	Down 6ft Rail	Down Cess Rail	Down 6ft Rail
60				Gradient					Gradient
61				Difference					Difference
62	0 (London)	19.72	19.718	3	1000	19.723	19.722	-1 3	4	750	0	-1 3	0.003	0.004	-0.001	0.001
63	1	19.701	19.702			19.703	19.706				0		0.002	0.004	0	0
64	2	19.682	19.683	0	---Level---	19.684	19.686	2	-1	-3000	0	2	0.002	0.003	0.001	0
65	3	19.663	19.662	-2	-1500	19.664	19.663	-1	-3	-1000	0	-1	0.001	0.001	0.003	0.001
66	4	19.644	19.648	5	600	19.645	19.65	5	6	500	0	5	0.001	0.002	0.001	0.001
67	5	19.627	19.633	2	1500	19.627	19.635	8	3	1000	0	8	0	0.002	0.001	-0.001
68	6	19.609	19.612	-3	-1000	19.609	19.615	6	-2	-1500	0	6	0	0.003	0	0.001
69	7	19.593	19.596	0	---Level---	19.593	19.599	6	0	---Level---	0	6	0	0.003	0.002	0.002
70	8	19.577	19.576	-4	-750	19.578	19.579	1	-5	-600	0	1	0.001	0.003	0.003	0.002
71	9	19.559	19.558	0	---Level---	19.559	19.56	1	0	---Level---	0	1	0	0.002	0.002	0.001
72	10	19.541	19.541	1	3000	19.541	19.543	2	1	3000	0	2	0	0.002	0.001	0.001
73	11	19.524	19.526	2	1500	19.524	19.529	5	3	1000	0	5	0	0.003	0.002	0.002
74	12	19.51	19.512	0	---Level---	19.510	19.514	4	-1	-3000	0	4	0	0.002	0.002	0.002
75	13	19.496	19.498	0	---Level---	19.496	19.5	4	0	---Level---	0	4	0	0.002	0.002	0.002
76	14	19.482	19.484	0	---Level---	19.482	19.486	4	0	---Level---	0	4	0	0.002	0.001	0.001
77	15	19.468	19.469	-1	-3000	19.468	19.47	2	-2	-1500	0	2	0	0.001	0.001	0.001
78	16	19.455	19.457	1	3000	19.455	19.458	3	1	3000	0	3	0	0.001	0.001	0.001
79	17	19.445	19.446	-1	-3000	19.445	19.448	3	0	---Level---	0	3	0	0.002	0.001	0.001
80	18	19.434	19.435	0	---Level---	19.433	19.437	4	1	3000	0	4	-0.001	0.002	0.001	0
81	19	19.421	19.424	2	1500	19.421	19.426	5	1	3000	0	5	0	0.002	0	-0.002
82	20 (Country)	19.414	19.414	-3	-1000	19.415	19.416	1	-4	-750	0	1	0.001	0.002	-0.002	-0.002
Sheet1

 

[KRice]

Welcome to the board! Do you always have 21 rows of data to plot (0,20)? And if we wanted to isolate a particular data set for plotting, how would we identify it...would it be by date using the dates appearing in each mini-table heading in column N?

 

[roryg90]

Thanks for the reply! Yes it is always 21 rows of data. Yes I identify them using the date in column N, just like in the example picture of the chart I shared. It is the same section of track that I monitor and the position references are where the nodes are on the railway sleepers. So I am just plotting their variation to the initial readings that were taken. But I get new results everyday, so the actual charts I plot have data since last November!

 

[KRice]

To the right of your mini-tables, I added the following:

MrExcel20210203.xlsx
	S	T	U	V	W
1	Trimmed Date & Notes	Worksheet structure details
2		top 1st data table, row # of sheet	4
3		total number of rows in tables	25
4	08/08/20	within table 1st row to plot	5
5		number rows to plot	21
6		spacing between tables, rows	2
7		column number to plot, x-axis	2	col. B
8		column number to plot, y-axis	14	col. N
9		location of date, column	14	1st row ea table
10
11			8/8/20 8:00	8/10/20 8:00	legend
12		4	4	58	minitable begins @row
13			Date to plot	Date to plot
14			08/08/20	08/10/20
15		Position Ref. (3m Centres)	Down Cess Rail	Down Cess Rail
16		0 (London)	0.002	0.003
17		1	0.002	0.002
18		2	0.001	0.002
19		3	0.001	0.001
20		4	0.001	0.001
21		5	0	0
22		6	0	0
23		7	0	0
24		8	0	0.001
25		9	0	0
26		10	0	0
27		11	0.001	0
28		12	0	0
29		13	0	0
30		14	0	0
31	08/09/20	15	0	0
32		16	-0.001	0
33		17	-0.001	0
34		18	-0.001	-0.001
35		19	-0.001	0
36		20 (Country)	0	0.001
roryg90

Cell Formulas
Range	Formula
U11:V11	U11	=INDEX($N$1:$N$82,U12)
T12	T12	=U12
U12:V12	U12	=MATCH(U14,$S$1:$S$82,0)
S2:S36	S2	=IF((TYPE(N2)=1)*(N2>40000),DATE(YEAR(N2),MONTH(N2),DAY(N2)),"")
T16:T36	T16	=OFFSET(INDIRECT(ADDRESS(T$12+$U$4-1,$U$7)),ROWS(T$16:T16)-1,0,1,1)
U16:V36	U16	=OFFSET(INDIRECT(ADDRESS(U$12+$U$4-1,$U$8)),ROWS(U$16:U16)-1,0,1,1)

Column S is used to extract a cleaner date found in column N at the top of each mini-table. Two comments about this: 1) your date/time is in a merged cell, which doesn't play well with certain formulas, and 2) the time stamp associated with the date seems to cause a mismatch problem with the MATCH formula I used to identify a particular mini-table of interest for plotting. As a work-around, I used a formula in column S to extract the date associated with each mini-table, except the time stamp is set to midnight. The implication is that for any mini-table that is appended to the worksheet, ensure that the formula in column S is present so that the trimmed/simplified date appears in column S. Then for any given input dates entered into the blue cells (U13:V13), the formulas in the helper block (T11:V12) reliably find the top of the specified mini-tables and the appropriate legend to use for each. The range T14:V35 uses OFFSET/INDIRECT/ADDRESS functions to copy data from each of the specified mini-tables into a standing data block that is used for plotting. You shouldn't have to do anything more than type the dates in U13:V13 and the plot will automatically update with the specified data and legends.