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

roryg90

New Member
Joined
Feb 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.

1612373588058.png


Monitoring Analysis example.xlsx
BCDEFGHIJKLMNOPQ
4Position Ref. (3m Centres)8/4/208/4/208/8/20 8:008/8/20 8:008/8/20 8:008/8/20 8:00
5Down Cess RailDown 6ft RailDown Main Down TwistDown Cess RailDown 6ft RailMeasured CantDown Main Twist ValuesDesign CantDiff to Design CantDown Cess RailDown 6ft RailDown Cess RailDown 6ft Rail
6Gradient Gradient
7DifferenceDifference
80 (London)19.7219.7183100019.72219.72-2 247500-2 20.0020.002-0.002-0.001
9119.70119.70219.70319.70500.0020.003-0.001-0.001
10219.68219.6830---Level---19.68319.68520---Level---020.0010.0020-0.002
11319.66319.662-2-150019.66419.662-2-4-7500-20.00100.001-0.002
12419.64419.648560019.64519.6557429050.0010.002-0.001-0.001
13519.62719.6332150019.62719.6358310000800.002-0.001-0.002
14619.60919.612-3-100019.60919.6145-3-10000500.002-0.0010
15719.59319.5960---Level---19.59319.59850---Level---0500.0020-0.001
16819.57719.576-4-75019.57719.5781-4-7500100.0020.0010
17919.55919.5580---Level---19.55919.5610---Level---0100.00200.001
181019.54119.5411300019.54119.5432130000200.0020.0010
191119.52419.5262150019.52519.528313000030.0010.0020.0010.001
201219.5119.5120---Level---19.51019.5144130000400.0020.0010.001
211319.49619.4980---Level---19.49619.4993-1-30000300.0010.0020
221419.48219.4840---Level---19.48219.48530---Level---0300.00100.001
231519.46819.469-1-300019.46819.4691-2-150001000.0010.002
241619.45519.4571300019.45419.45843100004-0.0010.0010.0010.002
251719.44519.446-1-300019.44419.4473-1-300003-0.0010.0010.0010.002
261819.43419.4350---Level---19.43319.43741300004-0.0010.00200.002
271919.42119.4242150019.4219.42662150006-0.0010.002-0.0010
2820 (Country)19.41419.414-3-100019.41419.4151-5-6000100.001-0.0010.001
29
30Vertical Variation To ExistingLateral Variation To Existing
31Position Ref. (3m Centres)8/4/208/4/208/9/20 9:008/9/20 9:008/9/20 9:008/9/20 9:00
32Down Cess RailDown 6ft RailDown Main Down TwistDown Cess RailDown 6ft RailMeasured CantDown Main Twist ValuesDesign CantDiff to Design CantDown Cess RailDown 6ft RailDown Cess RailDown 6ft Rail
33Gradient Gradient
34DifferenceDifference
350 (London)19.7219.7183100019.72219.72-2 247500-2 20.0020.00200
36119.70119.70219.70219.70400.0010.00200
37219.68219.6830---Level---19.68319.68520---Level---020.0010.0020.0010
38319.66319.662-2-150019.66319.662-1-3-10000-1000.0020
39419.64419.648560019.64419.65674290600.0020.0010.002
40519.62719.6332150019.62719.6358215000800.00200
41619.60919.612-3-100019.60919.6145-3-10000500.00200.001
42719.59319.5960---Level---19.59319.5996130000600.0030.0010.002
43819.57719.576-4-75019.57819.5780-6-500000.0010.0020.0020.001
44919.55919.5580---Level---19.56019.559-1-1-30000-10.0010.0010.002-0.001
451019.54119.5411300019.54219.543121500010.0010.0020.0010.002
461119.52419.5262150019.52519.528321500030.0010.0020.0010.002
471219.5119.5120---Level---19.51019.5144130000400.0020.0020.001
481319.49619.4980---Level---19.49619.4993-1-30000300.0010.0020
491419.48219.4840---Level---19.48219.48530---Level---0300.00100
501519.46819.469-1-300019.46819.4691-2-150001000-0.001
511619.45519.4571300019.45519.4583215000300.00100
521719.44519.446-1-300019.44519.4461-2-1500010000
531819.43419.4350---Level---19.43419.4362130000200.00100
541919.42119.4242150019.4219.42442150004-0.001000
5520 (Country)19.41419.414-3-100019.41419.4140-4-75000000-0.001
56
57Vertical Variation To ExistingLateral Variation To Existing
58Position Ref. (3m Centres)8/4/208/4/208/10/20 8:008/10/20 8:008/10/20 8:008/10/20 8:00
59Down Cess RailDown 6ft RailDown Main Down TwistDown Cess RailDown 6ft RailMeasured CantDown Main Twist ValuesDesign CantDiff to Design CantDown Cess RailDown 6ft RailDown Cess RailDown 6ft Rail
60Gradient Gradient
61DifferenceDifference
620 (London)19.7219.7183100019.72319.722-1 347500-1 30.0030.004-0.0010.001
63119.70119.70219.70319.70600.0020.00400
64219.68219.6830---Level---19.68419.6862-1-3000020.0020.0030.0010
65319.66319.662-2-150019.66419.663-1-3-10000-10.0010.0010.0030.001
66419.64419.648560019.64519.6556500050.0010.0020.0010.001
67519.62719.6332150019.62719.6358310000800.0020.001-0.001
68619.60919.612-3-100019.60919.6156-2-15000600.00300.001
69719.59319.5960---Level---19.59319.59960---Level---0600.0030.0020.002
70819.57719.576-4-75019.57819.5791-5-600010.0010.0030.0030.002
71919.55919.5580---Level---19.55919.5610---Level---0100.0020.0020.001
721019.54119.5411300019.54119.5432130000200.0020.0010.001
731119.52419.5262150019.52419.5295310000500.0030.0020.002
741219.5119.5120---Level---19.51019.5144-1-30000400.0020.0020.002
751319.49619.4980---Level---19.49619.540---Level---0400.0020.0020.002
761419.48219.4840---Level---19.48219.48640---Level---0400.0020.0010.001
771519.46819.469-1-300019.46819.472-2-15000200.0010.0010.001
781619.45519.4571300019.45519.4583130000300.0010.0010.001
791719.44519.446-1-300019.44519.44830---Level---0300.0020.0010.001
801819.43419.4350---Level---19.43319.43741300004-0.0010.0020.0010
811919.42119.4242150019.42119.4265130000500.0020-0.002
8220 (Country)19.41419.414-3-100019.41519.4161-4-750010.0010.002-0.002-0.002
Sheet1
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,138
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,138
Office Version
  1. 2019
Platform
  1. Windows
To the right of your mini-tables, I added the following:
MrExcel20210203.xlsx
STUVW
1Trimmed Date & NotesWorksheet structure details
2 top 1st data table, row # of sheet4
3 total number of rows in tables25
408/08/20within table 1st row to plot5
5 number rows to plot21
6 spacing between tables, rows2
7 column number to plot, x-axis2col. B
8 column number to plot, y-axis14col. N
9 location of date, column141st row ea table
10 
11 8/8/20 8:008/10/20 8:00legend
12 4458minitable begins @row
13 Date to plotDate to plot
14 08/08/2008/10/20
15 Position Ref. (3m Centres)Down Cess RailDown Cess Rail
16 0 (London)0.0020.003
17 10.0020.002
18 20.0010.002
19 30.0010.001
20 40.0010.001
21 500
22 600
23 700
24 800.001
25 900
26 1000
27 110.0010
28 1200
29 1300
30 1400
3108/09/201500
32 16-0.0010
33 17-0.0010
34 18-0.001-0.001
35 19-0.0010
36 20 (Country)00.001
roryg90
Cell Formulas
RangeFormula
U11:V11U11=INDEX($N$1:$N$82,U12)
T12T12=U12
U12:V12U12=MATCH(U14,$S$1:$S$82,0)
S2:S36S2=IF((TYPE(N2)=1)*(N2>40000),DATE(YEAR(N2),MONTH(N2),DAY(N2)),"")
T16:T36T16=OFFSET(INDIRECT(ADDRESS(T$12+$U$4-1,$U$7)),ROWS(T$16:T16)-1,0,1,1)
U16:V36U16=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.

1612388091316.png
 

Forum statistics

Threads
1,136,275
Messages
5,674,779
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top