How to automatically populate table

[deekay78]

Hi

I'm hoping someone can help please?

I have an excel spreadsheet and on one tab is an AM handover template and another tab a PM handover template, which has a number of sections. On an AM and PM basis (two shifts), a score will be manually added to the handover document by staff, in each of its sections. For example: Personal World score - 3, Physical Environment score - 5 and so on.
On another tab, this is a monthly tab, ready to fill in data for the whole of July, example 15 days. When someone scores a section I would like this to populate the table in the monthly tab automatically, in two separate tables, one for AM and one for PM. The total average scores for the month are then calculated daily. I'd then be adding new tabs month by month.

Appreciate any help possible here! Can provide pics if helpful. Thank you

AM handover:

handover example july.xlsx
	A	B	C	D	E	F	G	H
1	Relational Security Explorer (AM)		Rating No. 1 = weak 5 = great	Prompts	Patient Name	D.O.B	D.O.A	LoS
2	INSIDE WORLD	Personal World	3	xx
3				xx	Reason for Admission	Section	Diagnosis	Legal Status
4				xx
5					Risk Rag - RED MOAS (PICU)		Boundaries
6
7					Risk		Observation Levels
8
9				xx	A - Appearance & Behaviour:
10					S - Speech:
11					E - Emotion / mood /affect:
12					P - Perceptions:
13					T - Thoughts:
14					I - Insight:
15					C - Cognition:
16		Physical Environment	4	xx	Clinical Presentation
17				xx	Mental State
18				xx	Patient Perspective
19					Insight/Capacity/Consent to treatment
20					Physical Health
21					Plan
22	TEAM	Therapy	4		Medication
23					PRN
24					RT
25					Activities
26					Psychology
27		Boundaries	1		Health Outcomes
28					Evidence of Progress
29
30	OTHER PATIENTS	Patient Mix	2		Safeguarding
31
32
33		Patient Dynamic	4
34
35
36	OUTSIDE WORLD	Outward Connections	4		Section 17
37					Phone calls
38					Discharge Planning
39		Visitors	1		Referral
40
41
Handover AM example

PM Handover:

handover example july.xlsx
	A	B	C	D	E	F	G	H
1	Relational Security Explorer (PM)		Rating No. 1 = weak 5 = great	Prompts	Patient Name	D.O.B	D.O.A	LoS
2	INSIDE WORLD	Personal World	3	xx
3				xx	Reason for Admission	Section	Diagnosis	Legal Status
4				xx
5					Risk Rag - RED MOAS (PICU)		Boundaries
6
7					Risk		Observation Levels
8
9				xx	A - Appearance & Behaviour:
10					S - Speech:
11					E - Emotion / mood /affect:
12					P - Perceptions:
13					T - Thoughts:
14					I - Insight:
15					C - Cognition:
16		Physical Environment	4	xx	Clinical Presentation
17				xx	Mental State
18				xx	Patient Perspective
19					Insight/Capacity/Consent to treatment
20					Physical Health
21					Plan
22	TEAM	Therapy	4		Medication
23					PRN
24					RT
25					Activities
26					Psychology
27		Boundaries	1		Health Outcomes
28					Evidence of Progress
29
30	OTHER PATIENTS	Patient Mix	2		Safeguarding
31
32
33		Patient Dynamic	4
34
35
36	OUTSIDE WORLD	Outward Connections	4		Section 17
37					Phone calls
38					Discharge Planning
39		Visitors	1		Referral
40
41
Handover PM example

July Example:

handover example july.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Ward A - Explorer Rating Scores										Ward B - Explorer Rating Scores
2	AM SHIFT	Inside World		Team		Other Patients		Outside World			AM SHIFT	Inside World		Team		Other Patients		Outside World
3	Date	Personal World	Physical Environment	Therapy	Boundaries	Patient Mix	Patient Dynamic	Outward Connections	Visitors		Date	Personal World	Physical Environment	Therapy	Boundaries	Patient Mix	Patient Dynamic	Outward Connections	Visitors
4	01/07/2022	3	3	1	5	4	5	5	2		01/07/2022
5	02/07/2022	4	2	4	5	4	1	2	2		02/07/2022
6	03/07/2022	4	1	2	2	3	4	2	2		03/07/2022
7	04/07/2022	1	2	1	2	3	2	3	1		04/07/2022
8	05/07/2022	3	1	2	1	1	1	2	4		05/07/2022
9	06/07/2022	5	1	2	1	2	2	2	2		06/07/2022
10	07/07/2022	5	4	3	4	3	2	2	1		07/07/2022
11	08/07/2022	5	2	3	2	1	2	2	2		08/07/2022
12	09/07/2022	3	1	5	1	2	2	3	2		09/07/2022
13	10/07/2022	1	2	5	2	2	2	3	3		10/07/2022
14	11/07/2022	2	2	5	2	2	3	3	3		11/07/2022
15	12/07/2022	1	2	5	3	2	2	3	3		12/07/2022
16	13/07/2022	1	2	3	3	3	2	1	1		13/07/2022
17	14/07/2022	2	2	3	3	4	3	4	3		14/07/2022
18	15/07/2022	3	2	3	2	4	2	2	3		15/07/2022
19	Average Score	2.87	1.93	3.13	2.53	2.67	2.33	2.60	2.27		Average Score	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
20
21		Ward A - Explorer Rating Scores										Ward B - Explorer Rating Scores
22	PM SHIFT	Inside World		Team		Other Patients		Outside World			PM SHIFT	Inside World		Team		Other Patients		Outside World
23	Date	Personal World	Physical Environment	Therapy	Boundaries	Patient Mix	Patient Dynamic	Outward Connections	Visitors		Date	Personal World	Physical Environment	Therapy	Boundaries	Patient Mix	Patient Dynamic	Outward Connections	Visitors
24	01/07/2022										01/07/2022
25	02/07/2022										02/07/2022
26	03/07/2022										03/07/2022
27	04/07/2022										04/07/2022
28	05/07/2022										05/07/2022
29	06/07/2022										06/07/2022
30	07/07/2022										07/07/2022
31	08/07/2022										08/07/2022
32	09/07/2022										09/07/2022
33	10/07/2022										10/07/2022
34	11/07/2022										11/07/2022
35	12/07/2022										12/07/2022
36	13/07/2022										13/07/2022
37	14/07/2022										14/07/2022
38	15/07/2022										15/07/2022
39	Average Score	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!		Average	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
40
July example

Cell Formulas
Range	Formula
L39:S39,B39:I39,L19:S19,B19:I19	B19	=AVERAGE(B4:B18)