Select drop down list based on cell result

[monkeyharris]

Not sure how to explain what i need but here goes.
In cell F6, i want it to look at what's in cell F5, if F5 says "SMAT", i want F6 to have a drop list showing cells U2-U6, if F5 says "Hazard", i want F5 to have a drop down list showing cells V2-V6, if F5 says "Near Miss", i F6 to have a drop down list showing cells W2-W6.

Is this possible??

 

[dreid1011]

Yes, if you use a helper column and use that as your data validation source. In this case, I used column X to hold a lookup formula to pick which column U:W should be the source of the dropdown in F6.

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1																SMAT	HAZARD	NEAR MISS
2																U1	V1	W1	W1
3																U2	V2	W2	W2
4																U3	V3	W3	W3
5	NEAR MISS															U4	V4	W4	W4
6	W3															U5	V5	W5	W5
Sheet11

Cell Formulas
Range	Formula
X2:X6	X2	=XLOOKUP($F$5,$U$1:$W$1,$U$2:$W$6,"",0)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F6	List	=$X$2:$X$6

 

[monkeyharris]

Thanks Dreid1011, will try this now.

Thanks

 

[monkeyharris]

OK, so i've made a mistake. I was supposed to have the formula so it could be copied down in the column. What you sent worked for that one cell only.
This is what i meant to ask for.
User enters either SMAT, HAZARD or NEAR MISS in E6, E7, E8 and so on in column E. I want the corresponding cells from F6, F7, F8 and so on to lookup the bottom table. So if SMAT is in E6, F6 should have a dropdown list for the user to select: Safety, Environment, Observation, Hazard, Near Miss

2024 - Branch Monthly EHS Reports.xlsx
	E	F
5	Type	Classification
6	SMAT
7	Hazard
8	SMAT
Branch Reporting Log

Cells with Data Validation
Cell	Allow	Criteria
E6:E300	List	=$AF$3:$AF$5
F6:F8	List	=$AM$2:$AM$10

2024 - Branch Monthly EHS Reports.xlsx
	AJ	AK	AL
1	SMAT	HAZARD	NEAR MISS
2	Safety	Contact with moving machinery	Contact with moving machinery
3	Environment	Fall from height	Fall from height
4	Observation	Involving FLT	Involving FLT
5	Hazard	Manual handling	Manual handling
6	Near Miss	Slips, trips or falls	Slips, trips or falls
7		Struck against stationary object	Struck against stationary object
8		Struck by flying objects/debris	Struck by flying objects/debris
9		Struck by moving vehicle (not FLT)	Struck by moving vehicle (not FLT)
10		Other	Other
Branch Reporting Log

 

[dreid1011]

So in F6, add your data validation using the lookup formula as the source.

=XLOOKUP($F6,$AJ$1:$AL$1,$AJ$2:$AL$10,"",0)

Then you can drag and copy the data validation down as far as you need.