Dropdown formula or VB code required - Please Help

M

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
358
Office Version
  1. 365
Platform
  1. Windows
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
EF
5TypeClassification
6SMAT
7Hazard
8Near Miss
Branch Reporting Log
Cells with Data Validation
CellAllowCriteria
E6:E8List=$AF$3:$AF$5


2024 - Branch Monthly EHS Reports.xlsx
EF
5TypeClassification
6SMAT
7Hazard
8Near Miss
Branch Reporting Log
Cells with Data Validation
CellAllowCriteria
E6:E8List=$AF$3:$AF$5




2024 - Branch Monthly EHS Reports.xlsx
AJAKAL
1SMATHAZARDNEAR MISS
2SafetyFire exit blockedContact with moving machinery
3EnvironmentKeys left in ignitionFall from height
4ObservationPoor housekeepingInvolving FLT
5HazardProduct not secured properlyManual handling
6Near MissProduct not stacked properlySlips, trips or falls
7 Slip, trips, fall hazardStruck against stationary object
8 Walkway blockedStruck by flying objects/debris
9 Wet floorStruck by moving vehicle (not FLT)
10 OtherOther
Branch Reporting Log
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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
EF
1TypeClassification
2SMAT
3Hazard
4Near Miss
Dynamic DV
Cells with Data Validation
CellAllowCriteria
E2:E4List=$AF$3:$AF$5
F2:F5List=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))
 
Upvote 0
Solution
6StringJazzer said:
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
EF
1TypeClassification
2SMAT
3Hazard
4Near Miss
Dynamic DV
Cells with Data Validation
CellAllowCriteria
E2:E4List=$AF$3:$AF$5
F2:F5List=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))
Click to expand...
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.
 
Upvote 0
6StringJazzer said:
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
EF
1TypeClassification
2SMAT
3Hazard
4Near Miss
Dynamic DV
Cells with Data Validation
CellAllowCriteria
E2:E4List=$AF$3:$AF$5
F2:F5List=IF(E2="SMAT",$AJ$2:$AJ$6,IF(E2="HAZARD",$AK$2:$AK$10,IF(E2="Near Miss",$AL$2:$AL$10,$AI$1)))
Click to expand...
 
Upvote 0
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
EF
5TypeClassification
6SMATSafety
7HazardEnvironment
8Near MissObservation
9Hazard
10Near Miss
Branch Reporting Log
Cell Formulas
RangeFormula
F6:F10F6=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
CellAllowCriteria
F9:F10List=$AF$3:$AF$5
E6:E10List=$AF$3:$AF$5
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
393
Greenbehindthecells
G

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top