force drop-down list only when 1 of 6 possible answers in a different cell is met.

[CaptainChaos]

I have done some reading on dependent drop down lists, but everything I have found refers to when there are for example 2 or more choices in 1 drop-down cell, and whichever answer is selected forces another drop-down menu. My problem is similar to that but not identical.
In my situation, I need only to force a drop-down menu if 1 of 5 possible answers is true.
cell F8 has a drop-down menu showing choices as follows:
Crew,
Services,
Catering,
Operator,
Operator Services,
Other

The cell below (F9) asks for the function or Job title related to the entry made in F8. If any choice is made other than "Crew", then a drop-down is not required, and any value can be placed in the cell. However, if "Crew" was selected in F8, then a drop-down is required to ensure that only certain jobs are selected to ensure the spelling of the job is consistent to link it to a pay-roll sheet in the same workbook.

How do I ensure that a drop-down list of job-titles (EG: Electrician, ASST Electrician, Mechanic etc) is forced in CELL F9 if Crew is selected in CELL F8, but allows any value to be put in F9 if anything other than "Crew" is selected in F8.

Hope that is clear.

Any assistance would be appreciated.

 

[mumps]

It's hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[CaptainChaos]

Thanks Mumps. In an ideal world, I would upload a mini-sheet, however, I am on the computer at work and don't have the permissions to use third party software. I've uploaded a screenshot showing cells F8 and F9 in White (The remainder are colored to indicate no data has been put in and therefore unable to be sumitted until valid data is in the cells. The True and Falses in Column J are Data validation to do that, but not part of my question.

My problem is that in F8, using a drop down list I have the following options to select
Crew,
Services,
Catering,
Operator,
Operator Services,
Other

IF Crew is selected (as in the screenshot) in F8, then in Cell F9 I want a drop-down list of the job titles for the crew. (In this instance Rig manager has been selected from a drop down list). However, if anything other than "Crew" is selected in cell F8, then I no longer want a drop-down list in cell F9. There are far too many job titles to have for other categories, and they wouldn't be relevant to the reason I need the drop-down in Cell F9. The Drop-down if Crew are selected is to ensure that only valid job-titles are entered so that only valid information is sent to the pay-roll sheet.

Hope that is clear.

 

[mumps]

I assume that you have a list of categories and job titles in your sheet as I see in the picture in your original post. In which ranges are the two lists found?

 

[CaptainChaos]

Mumps. Yes, on a seperate worksheet, I have tables for Data-validation look-ups. In the original picture I posted you can see several, but the ones that are relevant have the title of Category at the top (Lookup for Cell F8) and the other that would be used is called "Function / Job Title, which I would only want to be referenced if "Crew" was selected from the drop-down in F8. Otherwise in Cell F9, I want to be free to type anything.

 

[mumps]

I need to know that sheet's name and the ranges where the Category and Function / Job Title are listed in that sheet.

 

[John_w]

This should be achievable with a combination of Worksheet_Change and Worksheet_SelectionChange event handlers in the sheet module of the sheet containing the F8 and F9 cells. See if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$8" Then
            If Target.Count = 1 And Target.Value <> "CREW" Then
            'F8 not CREW, so delete F9 data validation (and F9 cell value?)
            Application.EnableEvents = False
            Range("F9").Validation.Delete
            'Range("F9").ClearContents
            Application.EnableEvents = True
        End If
    End If
       
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address = "$F$9" And Target.Count = 1 Then
 
        Application.EnableEvents = False
        Target.Validation.Delete
        If Range("F8").Value = "CREW" Then
            'F8 is CREW, so create F9 data validation dropdown
            With Target.Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Function_Table"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .InputTitle = "Select Function - Job Title"
                .InputMessage = "Select item from 'Function - Job Title' F9 dropdown"
                .ShowError = True
                .ErrorTitle = "Select Function - Job Title"
                .ErrorMessage = "CREW is selected in F8, so you must select an item from the F9 dropdown"
            End With
        End If
        Application.EnableEvents = True

    End If
     
End Sub

The table for the 'Function - Job Title' data list is named 'Function' and the name defined for it in Name Manager is 'Function_Table' (using Method 2 at https://www.myonlinetraininghub.com/excel-tables-as-source-for-data-validation-lists) and used as the Source (Formula1:="=Function_Table") in the Worksheet_SelectionChange code which creates the F9 data validation when F8 = CREW. I've included input and error titles and messages, but these are all optional.

What should happen if the user returns to F8 and selects something other than CREW from the dropdown? Currently, the Worksheet_Change code deletes the F9 data validation and retains the F9 cell value - the code to clear it is commented out.

 

[CaptainChaos]

Mumps. The worksheet with the data on is called "List"

The header of the table titled Function is cell A1, and the list of job titles in the table goes from Cell A2 to A28 (though as a table it can be expanded). The Category Title is in cell C1 and the options go from C2 to C7.

 

[CaptainChaos]

John.
Thanks for that. VBA references the cells I am putting the data in, but I have only very recently dipped my toe into VBA, and so the VBA I have is very basic. Most of the data validation to ensure that only clean data is submitted is done using the True/False in Cells I5 to I17, and the True and False in column J to highlight if any invalid data is submitted or nothing is submitted in cells that require an entry. I don't have the time to look at a VBA solution at the moment, as I have been on shift for 11 hours now, but if time permits I will try to look at it tomorrow.

Thanks for your help though, it is appreciated.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell F8.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "F8" Then Exit Sub
    If Target = "CREW" Then
        With Range("F9").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=OFFSET('List'!$A$2,0,0,COUNTA('List'!$A:$A),1)"
        End With
    Else
        Range("F9").Validation.Delete
        Range("F8").Select
    End If
End Sub