Formulae jumping references?

[welshgasman]

Blood Pressures.xlsm
	A	B	C	D	E	F	G	H	I
1	DateTime	Systolic	Dystolic	Heart		With Time	AVG Sys	AVG Dis	AVG Pulse
2	26/09	164	86			26/09/2024 14:15	141	78	71
3	26/09	120	63			26/09/2024 23:15
4	27/09	155	82			27/09/2024 09:25
5	27/09	134	76	73		27/09/2024 14:15
6	27/09	143	89	58		27/09/2024 22:58
7	28/09	156	92	59		28/09/2024 08:15
8	29/09	147	78	80		29/09/2024 10:02
9	29/09	127	82	61		29/09/2024 13:05
10	29/09	155	89	91		29/09/2024 19:38
11	29/09	147	75	76		29/09/2024 21:53
12	30/09	145	80	64		30/09/2024 08:36
13	30/09	153	90	51		30/09/2024 12:29
14	30/09	141	79	64		30/09/2024 22:20
15	01/10	156	85	67		01/10/2024 08:48
16	01/10	138	84	73		01/10/2024 13:45
17	01/10	146	71	75		01/10/2024 22:22
18	02/10	140	72	71		02/10/2024 07:44
19	02/10	151	73	58		02/10/2024 15:14
20	03/10	142	69	73		03/10/2024 00:20
21	03/10	150	83	67		03/10/2024 09:42
22	03/10	144	81	62		03/10/2024 15:04
23	04/10	129	70	87		04/10/2024 08:07
24	04/10	133	71	76		04/10/2024 12:45
25	04/10	142	76	58		04/10/2024 22:43
26	05/10	135	76	69		05/10/2024 08:39
27	05/10	138	70	81		05/10/2024 22:58
28	06/10	148	77	78		06/10/2024 09:59
29	06/10	113	63	87		06/10/2024 20:58
30	07/10	140	83	93		07/10/2024 08:54
31	07/10	129	71	78		07/10/2024 12:28
32	07/10	141	88	76		07/10/2024 23:25
33	08/10	152	88	75		08/10/2024 09:37
34	08/10	128	68	67		08/10/2024 15:22
35	08/10	145	72	73		08/10/2024 21:48
36	09/10	149	80	66		09/10/2024 08:26
37	09/10	158	94	60		09/10/2024 15:46
38	09/10	122	66	75		09/10/2024 22:23
39	10/10	130	75	73		10/10/2024 08:45
40	10/10	140	88	59		10/10/2024 12:27
41	10/10	129	72	68		10/10/2024 23:06
42	11/10	141	85	75		11/10/2024 07:06
43	11/10	131	73	87		11/10/2024 13:09
44	11/10	131	74	67		11/10/2024 22:46
45	12/10	156	87	60		12/10/2024 09:11
46	12/10	132	73	72		12/10/2024 13:00
47						00/01/1900 00:00
48						00/01/1900 00:00
49						00/01/1900 00:00
50						00/01/1900 00:00
51						00/01/1900 00:00
52						00/01/1900 00:00
53						00/01/1900 00:00
54						00/01/1900 00:00
55						00/01/1900 00:00
56						00/01/1900 00:00
57
58
59
60
61
62
63
64
65
66
qryPressure

Cell Formulas
Range	Formula
G2:I2	G2	=AVERAGE(qryPressure!B:B)
F2:F56	F2	=A2

Hi all,
I have to keep track of my blood pressure now.
I do so with an Access database which I created to keep track of my carbs as I am type 2 diabetic.

The sheet gets it's data from a query in the database.
From this sheet, I created a column graph.

All good so far.
However the labels get cluttered if I use the full date format of the date on the chart, so I only use dd/mm format.
In another column F, I use ==A2 as the formula starts at row 2, and copy it down as far as the table exists at that time. I also copy it past that last row.

At the time of copy the referencing is correct, so if the data goes to row 46, F46 has = A46 and F47 has =A47 and so on.
However when I refresh the data, the references fo awry. It appears to be by the number of new rows added to the table?
So now F47 has F51 if I have just added 4 rows on the refresh.?

Is there a way I can keep the data synced on each refresh? I have added the refresh to the workbook.Open event, so as not to have to do it manually each time, but it was doing it, when I am doing it manually.
I can correct it by finding the last row with the correct reference, and then double clicking to copy down to the new end of data row.

I suppose I could copy F2 down as far as the rows goes, but is there an automatic way please?

TIA

 

[Sergius]

Hello! How about this F2=$A2
In this case, when added, the formula will work correctly.

 

[welshgasman]

Hi,
Thanks for the reply.

No, the column character is remaining the same, just the row number is incremented incorrectly.
Right now, with what I posted F46 = A46 and F47 is still = A47, even though there is not entry in column A yet. Same for the rows below that row.
However when I carry out a reading tonight, they will go awry again when I Refresh All.
Row 47 will remain as 00/01/1900 as that reference will change to A48 or later, (I think depending on how many new rows come in), which will not have anything in it.

 

[Sergius]

Use this formula for column F

=IF(A2<>"";A2;"")

 

[welshgasman]

Thank you.
I will try tomorrow.
I not get email notifications of any reply, despite them being set in preferences?

 

[welshgasman]

Thank you.
I will try tomorrow.
I not get email notifications of any reply, despite them being set in preferences?

No, same issue.
I could not use this morning as the workbook refreshes on open.
Just added an entry now to the DB and the same problem occurred, the formula for row 49 went to 50, so showed nothing.

I will just copy from F2 to the last row as per column F.

Thanks for trying.

 

[Sergius]

Hello! Alternatively, transform your flat table into a smart table. Subsequently, it will only be enough to expand it with the help of a marker by the number of lines that you need. As a result, all formulas will be automatically copied to new cells.

 

[welshgasman]

Thank you @Sergius
I added the copy range option ,and will likely leave it at that.