Dropdown formula or VB code required - Please Help

[monkeyharris]

I've been going round in circles but can't figure out how to get a result. In my worksheet the user enters the type of incident in E6, E7, E8, E9 and so on (ONLY SMAT, Hazard or Near Miss), but i need the cells in F6, F7, F8, E9 and so on to show different dropdown lists based on what's in the adjoining E cell. Drop down lists with SMAT, Hazard or Near Miss headers below.

Please HELP, how can i do this???

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

Cells with Data Validation
Cell	Allow	Criteria
E6:E8	List	=$AF$3:$AF$5

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

Cells with Data Validation
Cell	Allow	Criteria
E6:E8	List	=$AF$3:$AF$5

2024 - Branch Monthly EHS Reports.xlsx
	AJ	AK	AL
1	SMAT	HAZARD	NEAR MISS
2	Safety	Fire exit blocked	Contact with moving machinery
3	Environment	Keys left in ignition	Fall from height
4	Observation	Poor housekeeping	Involving FLT
5	Hazard	Product not secured properly	Manual handling
6	Near Miss	Product not stacked properly	Slips, trips or falls
7		Slip, trips, fall hazard	Struck against stationary object
8		Walkway blocked	Struck by flying objects/debris
9		Wet floor	Struck by moving vehicle (not FLT)
10		Other	Other
Branch Reporting Log

 

[6StringJazzer]

Use the data validation formula as shown and copy as needed. Note that the formula shown here is for cell F2. You could select all of the cells in column F starting in row 2 and apply that formula, or you could apply in F2 then copy and paste.

Note the reference to $AI$1. It assumes this is a blank cell. The result will be an empty list if the cell in E has not yet been selected, or is an invalid value.

This formula is hard-coded to your specific ranges. It is possible to write a more sophisticated formula that will work on any ranges with any lists, but I thought this would more directly get your problem solved.

$scratch.xlsm
	E	F
1	Type	Classification
2	SMAT
3	Hazard
4	Near Miss
Dynamic DV

Cells with Data Validation
Cell	Allow	Criteria
E2:E4	List	=$AF$3:$AF$5
F2:F5	List	=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))

 

[monkeyharris]

Use the data validation formula as shown and copy as needed. Note that the formula shown here is for cell F2. You could select all of the cells in column F starting in row 2 and apply that formula, or you could apply in F2 then copy and paste.

Note the reference to $AI$1. It assumes this is a blank cell. The result will be an empty list if the cell in E has not yet been selected, or is an invalid value.

This formula is hard-coded to your specific ranges. It is possible to write a more sophisticated formula that will work on any ranges with any lists, but I thought this would more directly get your problem solved.

$scratch.xlsm
	E	F
1	Type	Classification
2	SMAT
3	Hazard
4	Near Miss
Dynamic DV

Cells with Data Validation
Cell	Allow	Criteria
E2:E4	List	=$AF$3:$AF$5
F2:F5	List	=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))

Hi, the problem is the user will choose between SMAT, HAZARD, NEAR MISS continuously downloads. IE E2, E3, E4, E5, E6, E7, E8 etc etc. I was looking for F2, F3 etc downwards to provide a dropdown with what i've mentioned. Is this possible.

 

[monkeyharris]

Use the data validation formula as shown and copy as needed. Note that the formula shown here is for cell F2. You could select all of the cells in column F starting in row 2 and apply that formula, or you could apply in F2 then copy and paste.

Note the reference to $AI$1. It assumes this is a blank cell. The result will be an empty list if the cell in E has not yet been selected, or is an invalid value.

This formula is hard-coded to your specific ranges. It is possible to write a more sophisticated formula that will work on any ranges with any lists, but I thought this would more directly get your problem solved.

$scratch.xlsm
	E	F
1	Type	Classification
2	SMAT
3	Hazard
4	Near Miss
Dynamic DV

Cells with Data Validation
Cell	Allow	Criteria
E2:E4	List	=$AF$3:$AF$5
F2:F5	List	=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))

 

[monkeyharris]

Will try this now. Just for your info, when i did it from your first message, thsi is what my sheet looked like. Thought i did something wrong.

2024 - Branch Monthly EHS Reports.xlsx
	E	F
5	Type	Classification
6	SMAT	Safety
7	Hazard	Environment
8	Near Miss	Observation
9		Hazard
10		Near Miss
Branch Reporting Log

Cell Formulas
Range	Formula
F6:F10	F6	=IF(E6="SMAT",$AJ$2:$AJ$6,IF(E6="HAZARD",$AK$2:$AK$10,IF(E6="Near Miss",$AL$2:$AL$10,$AI$1)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F9:F10	List	=$AF$3:$AF$5
E6:E10	List	=$AF$3:$AF$5

 

[6StringJazzer]

Yes, that is not correct. In column F, you put the formula in the cell. Instead, you are supposed to create a data validation rule, using a List, then use that formula as the List formula.

Look at the difference between what shown in my post #2 for Data Validation, and what your post shows in #5.

Here is a link to my sample file.

Loading Google Sheets