Risk Assessment

[roblouk]

I have the below risk assessment workbook, the first sheet has questions and dropdown lists of answers for each question then a column to display the risk score for each answer. The second sheet is the answers for each question to go in the dropdown lists and the third sheet is the risk scores assigned to each answer. This list of questions and answers extends considerably so I am looking for a formula I can use to pull the risk score into column E based on the answers to the question.

I am not particularly fussy about the layout or formatting if there is a more sensible way to do this but I am hoping there is an easy way I can pull the information across. The risk scoring, questions and responses need to be kept out of the formula so they can be changed if required.

Hopefully this makes sense and thank you for the help!

Falls Care Quality Assessment (FCQA).xlsx
	A	B	C	D	E
1	Number	Question Text	Question Notes	Responses	Risk Score
2	1	What did the patient say happened (where appropriate or possible to ascertain)?	Please give a summary from the patient’s perspective.	Free Text
3	2	What did staff say happened?	Please include any information you believe may have contributed to the fall including: Where members of staff were at the time of the fall, ward acuity, anything different on this shift, any changes in the patient.	Free Text
4	3	Was the fall witnessed?	The fall was witnessed if another person was present when the patient fell and is able to give a reliable account of what they observed immediately before, at the point of the fall and immediately afterwards.
5	4	Was the patient on their own at the time of the fall?	If the patient was in a location with another patient or visitor but no staff or family/friends were present, answer that the patient was on their own. If a member of staff or family member was in the same room or bay but did not have the patient in their sight line (i.e. the patient was behind a curtain or door), consider the patient to be on their own.
6	5	What was the patient doing at the time of the fall?	If the patient was in the process of getting up or sitting down from the bed / chair / commode, choose “transferring between the bed / chair / commode”.
Questions

Cells with Data Validation
Cell	Allow	Criteria
D4	List	=Responses!$C$2:$C$3
D5	List	=Responses!$D$2:$D$4
D6	List	=Responses!$E$2:$E$9

Falls Care Quality Assessment (FCQA).xlsx
	A	B	C	D	E
1	1	2	3	4	5
2	Free text	Free text	Yes	The patient was on their own	Lying/sitting in the bed
3			No	The patient was with a member of staff	Sitting in a chair
4				The patient was with a family member or friend	Using a commode
5					Transferring between the bed/chair/commode
6					Walking on the ward
7					Using the toilet/bathroom
8					Not on the ward at the time of the fall
9					Not known as the fall was unwitnessed
Responses

Falls Care Quality Assessment (FCQA).xlsx
	A	B	C
1	Question	Answers	Risk Score
2	What did the patient say happened (where appropriate or possible to ascertain)?	1
3		Free text	N/A
4	What did staff say happened?	2
5		Free text	N/A
6	Was the fall witnessed?	3
7		Yes	0
8		No	0
9	Was the patient on their own at the time of the fall?	4
10		The patient was on their own	0
11		The patient was with a member of staff	1
12		The patient was with a family member or friend	1
13	What was the patient doing at the time of the fall?	5
14		Lying/sitting in the bed	1
15		Sitting in a chair	1
16		Using a commode	1
17		Transferring between the bed/chair/commode	0
18		Walking on the ward	0
19		Using the toilet/bathroom	0
20		Not on the ward at the time of the fall	0
21		Not known as the fall was unwitnessed	0
Risk Scoring

 

[fjns]

Hi, try this formula for E2 cell (Range: E2:E6)
=IF(A2<3,"N/A",VLOOKUP(D2,'Risk Scoring'!B:C,2,0))