Running Totals

[itsgrady]

I have a spreadsheet with days of the week as the header for each column.

What I would like to do is use the last column (7th column) for current information. The seventh column will always be the current date - the other 6 columns will be the previous days.

The next day I would like for all data move over to the left and the oldest day roll off the form. So, the current 7 days will always be on the form.

Is there an easy formula to move everything over each day. I was thinking to use a button with VBA code, unless there is an easier way.

Thanks for the help.

 

[JamesCanale]

I'd store all the data in some TABLE somewhere and just show the last seven days.

In K2 I use today. But I might also use what is in K1 for that instead.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Value									Tue 12/13/2022
2	11/15/2022	86		Day	Wed 12/7/2022	Thu 12/8/2022	Fri 12/9/2022	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022
3	11/16/2022	70		Value	90	81	16	20	11	40	90
4	11/17/2022	11
5	11/18/2022	23
6	11/19/2022	79
7	11/20/2022	89
8	11/21/2022	62
9	11/22/2022	66
10	11/23/2022	88
11	11/24/2022	60
12	11/25/2022	99
13	11/26/2022	77
14	11/27/2022	75
15	11/28/2022	61
16	11/29/2022	91
17	11/30/2022	93
18	12/1/2022	57
19	12/2/2022	14
20	12/3/2022	76
21	12/4/2022	79
22	12/5/2022	72
23	12/6/2022	40
24	12/7/2022	90
25	12/8/2022	81
26	12/9/2022	16
27	12/10/2022	20
28	12/11/2022	11
29	12/12/2022	40
30	12/13/2022	90
Sheet28

Cell Formulas
Range	Formula
K1	K1	=MAX(Table3[Date])
E2:J2	E2	=F2-1
K2	K2	=TODAY()
E3:K3	E3	=XLOOKUP(E2,Table3[[Date]:[Date]],Table3[[Value]:[Value]],"",0)

 

[itsgrady]

I'd store all the data in some TABLE somewhere and just show the last seven days.

In K2 I use today. But I might also use what is in K1 for that instead.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Value									Tue 12/13/2022
2	11/15/2022	86		Day	Wed 12/7/2022	Thu 12/8/2022	Fri 12/9/2022	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022
3	11/16/2022	70		Value	90	81	16	20	11	40	90
4	11/17/2022	11
5	11/18/2022	23
6	11/19/2022	79
7	11/20/2022	89
8	11/21/2022	62
9	11/22/2022	66
10	11/23/2022	88
11	11/24/2022	60
12	11/25/2022	99
13	11/26/2022	77
14	11/27/2022	75
15	11/28/2022	61
16	11/29/2022	91
17	11/30/2022	93
18	12/1/2022	57
19	12/2/2022	14
20	12/3/2022	76
21	12/4/2022	79
22	12/5/2022	72
23	12/6/2022	40
24	12/7/2022	90
25	12/8/2022	81
26	12/9/2022	16
27	12/10/2022	20
28	12/11/2022	11
29	12/12/2022	40
30	12/13/2022	90
Sheet28

Cell Formulas
Range	Formula
K1	K1	=MAX(Table3[Date])
E2:J2	E2	=F2-1
K2	K2	=TODAY()
E3:K3	E3	=XLOOKUP(E2,Table3[[Date]:[Date]],Table3[[Value]:[Value]],"",0)

I like that suggestion very much. Now, each column has 15 rows of data. Is there a formula to copy the row of information to each column since the dates are on the left column.

 

[JamesCanale]

This should do the trick - I made it for four columns of data

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Value	Thing	Else	But										Wed 12/14/2022
2	11/15/2022	86	88	176	126			Day	Thu 12/8/2022	Fri 12/9/2022	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022	Wed 12/14/2022
3	11/16/2022	70	72	144	94			Value	81	16	20	11	40	90	50
4	11/17/2022	11	13	26	-24			Thing	83	18	22	13	42	92	52
5	11/18/2022	23	25	50	0			Else	166	36	44	26	84	184	104
6	11/19/2022	79	81	162	112			But	116	-14	-6	-24	34	134	54
7	11/20/2022	89	91	182	132
8	11/21/2022	62	64	128	78
9	11/22/2022	66	68	136	86
10	11/23/2022	88	90	180	130
11	11/24/2022	60	62	124	74
12	11/25/2022	99	101	202	152
13	11/26/2022	77	79	158	108
14	11/27/2022	75	77	154	104
15	11/28/2022	61	63	126	76
16	11/29/2022	91	93	186	136
17	11/30/2022	93	95	190	140
18	12/1/2022	57	59	118	68
19	12/2/2022	14	16	32	-18
20	12/3/2022	76	78	156	106
21	12/4/2022	79	81	162	112
22	12/5/2022	72	74	148	98
23	12/6/2022	40	42	84	34
24	12/7/2022	90	92	184	134
25	12/8/2022	81	83	166	116
26	12/9/2022	16	18	36	-14
27	12/10/2022	20	22	44	-6
28	12/11/2022	11	13	26	-24
29	12/12/2022	40	42	84	34
30	12/13/2022	90	92	184	134
31	12/14/2022	50	52	104	54
Sheet28

Cell Formulas
Range	Formula
O1	O1	=MAX(Table3[Date])
C2:C31	C2	=[@Value]+2
D2:D31	D2	=[@Thing]*2
E2:E31	E2	=[@Else]-50
I2:N2	I2	=J2-1
O2	O2	=TODAY()
I3:O6	I3	=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))
Dynamic array formulas.

 

[itsgrady]

This should do the trick - I made it for four columns of data

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Value	Thing	Else	But										Wed 12/14/2022
2	11/15/2022	86	88	176	126			Day	Thu 12/8/2022	Fri 12/9/2022	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022	Wed 12/14/2022
3	11/16/2022	70	72	144	94			Value	81	16	20	11	40	90	50
4	11/17/2022	11	13	26	-24			Thing	83	18	22	13	42	92	52
5	11/18/2022	23	25	50	0			Else	166	36	44	26	84	184	104
6	11/19/2022	79	81	162	112			But	116	-14	-6	-24	34	134	54
7	11/20/2022	89	91	182	132
8	11/21/2022	62	64	128	78
9	11/22/2022	66	68	136	86
10	11/23/2022	88	90	180	130
11	11/24/2022	60	62	124	74
12	11/25/2022	99	101	202	152
13	11/26/2022	77	79	158	108
14	11/27/2022	75	77	154	104
15	11/28/2022	61	63	126	76
16	11/29/2022	91	93	186	136
17	11/30/2022	93	95	190	140
18	12/1/2022	57	59	118	68
19	12/2/2022	14	16	32	-18
20	12/3/2022	76	78	156	106
21	12/4/2022	79	81	162	112
22	12/5/2022	72	74	148	98
23	12/6/2022	40	42	84	34
24	12/7/2022	90	92	184	134
25	12/8/2022	81	83	166	116
26	12/9/2022	16	18	36	-14
27	12/10/2022	20	22	44	-6
28	12/11/2022	11	13	26	-24
29	12/12/2022	40	42	84	34
30	12/13/2022	90	92	184	134
31	12/14/2022	50	52	104	54
Sheet28

Cell Formulas
Range	Formula
O1	O1	=MAX(Table3[Date])
C2:C31	C2	=[@Value]+2
D2:D31	D2	=[@Thing]*2
E2:E31	E2	=[@Else]-50
I2:N2	I2	=J2-1
O2	O2	=TODAY()
I3:O6	I3	=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))
Dynamic array formulas.

I really appreciate the help on this project... Your way is a lot better because it keeps an history on a table. Thank you so much! Merry Christmas!

 

[itsgrady]

This should do the trick - I made it for four columns of data

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Value	Thing	Else	But										Wed 12/14/2022
2	11/15/2022	86	88	176	126			Day	Thu 12/8/2022	Fri 12/9/2022	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022	Wed 12/14/2022
3	11/16/2022	70	72	144	94			Value	81	16	20	11	40	90	50
4	11/17/2022	11	13	26	-24			Thing	83	18	22	13	42	92	52
5	11/18/2022	23	25	50	0			Else	166	36	44	26	84	184	104
6	11/19/2022	79	81	162	112			But	116	-14	-6	-24	34	134	54
7	11/20/2022	89	91	182	132
8	11/21/2022	62	64	128	78
9	11/22/2022	66	68	136	86
10	11/23/2022	88	90	180	130
11	11/24/2022	60	62	124	74
12	11/25/2022	99	101	202	152
13	11/26/2022	77	79	158	108
14	11/27/2022	75	77	154	104
15	11/28/2022	61	63	126	76
16	11/29/2022	91	93	186	136
17	11/30/2022	93	95	190	140
18	12/1/2022	57	59	118	68
19	12/2/2022	14	16	32	-18
20	12/3/2022	76	78	156	106
21	12/4/2022	79	81	162	112
22	12/5/2022	72	74	148	98
23	12/6/2022	40	42	84	34
24	12/7/2022	90	92	184	134
25	12/8/2022	81	83	166	116
26	12/9/2022	16	18	36	-14
27	12/10/2022	20	22	44	-6
28	12/11/2022	11	13	26	-24
29	12/12/2022	40	42	84	34
30	12/13/2022	90	92	184	134
31	12/14/2022	50	52	104	54
Sheet28

Cell Formulas
Range	Formula
O1	O1	=MAX(Table3[Date])
C2:C31	C2	=[@Value]+2
D2:D31	D2	=[@Thing]*2
E2:E31	E2	=[@Else]-50
I2:N2	I2	=J2-1
O2	O2	=TODAY()
I3:O6	I3	=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))
Dynamic array formulas.

I'm having a difficult setting up my array... I guess the define names are throwing me off some... I can the column data to return to one cell on the table but not sure how to fill the entire column in the table.

 

[JamesCanale]

See where I have this:
Table3[[Value]:[But]]
This is actually pulling an array, not just one cell into the table in columns I to O. It's outputting everything in the matching row from [Value] to [But]. Then I transpose it to make it go vertical.

 

[itsgrady]

See where I have this:
Table3[[Value]:[But]]
This is actually pulling an array, not just one cell into the table in columns I to O. It's outputting everything in the matching row from [Value] to [But]. Then I transpose it to make it go vertical.

 

[itsgrady]

View attachment 80987

I had a hard time follow the vlookup formula---excel says that I have to many arguments. I count five... I like your solution but I am struggling or just tried.

Thanks for your help.

 

[JamesCanale]

MrExcelPlayground14.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2												Aspirate Inv				Pounds Incinterated
3											Date	Acid	Base	Oxidizer	React	T1 day	t1 night	t2day	t2night
4											Tuesday, December 6, 2022	10	46	96	55	210	20	78	48
5	Aspriate	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022	Wed 12/14/2022	Thu 12/15/2022	Fri 12/16/2022			Wednesday, December 7, 2022	11	97	21	69	710	24	68	85
6	Acid	16	80	45	33	44	72	88			Thursday, December 8, 2022	75	67	84	29	500	25	98	49
7	Base	92	30	71	52	93	51	58			Friday, December 9, 2022	72	78	45	42	350	72	53	53
8	Oxidizer	94	62	86	64	33	25	56			Saturday, December 10, 2022	16	92	94	39	800	92	71	97
9	React	39	44	88	35	82	40	18			Sunday, December 11, 2022	80	30	62	44	480	26	13	40
10	Aspirate Tot	241	216	290	184	252	188	220			Monday, December 12, 2022	45	71	86	88	410	41	72	54
11											Tuesday, December 13, 2022	33	52	64	35	740	92	33	90
12	Total	Sat 12/10/2022	Sun 12/11/2022	Mon 12/12/2022	Tue 12/13/2022	Wed 12/14/2022	Thu 12/15/2022	Fri 12/16/2022			Wednesday, December 14, 2022	44	93	33	82	960	93	92	85
13	T1 day	800	480	410	740	960	800	970			Thursday, December 15, 2022	72	51	25	40	800	53	90	66
14	t1 night	92	26	41	92	93	53	81			Friday, December 16, 2022	88	58	56	18	970	81	16	97
15	t2day	71	13	72	33	92	90	16
16	t2night	97	40	54	90	85	66	97
17	Tot	1060	559	577	955	1230	1009	1164
Sheet31

Cell Formulas
Range	Formula
E5:J5,E12:J12	E5	=F5-1
K5,K12	K5	=MAX(Table1[Date])
E6:K9	E6	=TRANSPOSE(XLOOKUP(E5,Table1[[Date]:[Date]],Table1[[Acid]:[React]],"",0))
E10:K10,E17:K17	E10	=SUM(E6#)
E13:K16	E13	=TRANSPOSE(XLOOKUP(E12,Table1[[Date]:[Date]],Table1[[T1 day]:[t2night]],"",0))
N5:N14	N5	=N4+1
Dynamic array formulas.