Sheet with conditional dropdown to select specific range according to choice to change results displayed

[VBXL]

Originally posted here: https://www.mrexcel.com/board/threa...o-choice-to-change-results-displayed.1247332/

I have a sheet containing data. There are two dropdown boxes. I only want data to display in E4 & F4 once a choice has been made in the dropdown box in C4 AND D4.

When CT is chosen in the first dropdown, I want the subsequent dropdown to be looking through data range D5:D169. If DNN, data range D170:D334.

The difficulty I am having is that some areas have two different "Inspectors" and it's only taking data from the first range, even when DNN is selected.

I'm not sure what to do to get this working.

I have the data listed immediately under the dropdown box in order to utilise autocomplete, so the data will be in hidden rows once this is finished.

Testy.xlsm​

I haven't managed to get it to work and it doesn't appear to run as no debug prompts are coming up.

VBA Code:
' Module containing validation logic for the VOP workbook

' Private Sub Worksheet_Change(ByVal Target As Range)
'
' This event is triggered whenever the value of any cell on the worksheet is changed.
'
' The code in this event should be designed to handle all possible changes, including changes to cells other than C4, D4, and E4.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

' If the target cell is C4, D4, or E4, update the validation list in E4
If Target.Address = Range("C4").Address Or Target.Address = Range("D4").Address Or Target.Address = Range("E4").Address Then
UpdateValidationList
End If

Exit Sub

ErrorHandler:
Debug.Print "Error: " & Err.Description

End Sub

' Sub UpdateValidationList()
'
' This sub updates the validation list in cell E4 based on the values in cells C4 and D4.
'

Sub UpdateValidationList()
On Error GoTo ErrorHandler

Dim ParishList As Range
Dim ParishCriteria As String
Dim SourceRange As Range
Dim TargetCell As Range

' Define the Parish list and criteria (changing A to E)
Set ParishList = Worksheets("VO Areas").Range("E5:E334")
ParishCriteria = Range("C4").Value & Range("D4").Value

' Clear previous data validation
With Range("E4").Validation
.Delete
End With

' Set the source range based on criteria
On Error Resume Next
Set SourceRange = ParishList.Worksheet.Range("E" & ParishList.Find(ParishCriteria).Row & ":E" & ParishList.Rows.Count)
On Error GoTo 0

' Check if the source range is empty
If SourceRange Is Nothing Then
' Display an error message if the source range is empty
MsgBox "The source range is empty. Please select a valid Parish and Area."
Exit Sub
End If

' Apply data validation based on the source range
With Range("E4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(Application.Transpose(SourceRange.Value), ",")
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

Exit Sub

ErrorHandler:
Debug.Print "Error in UpdateValidationList: " & Err.Description

End Sub

' Sub UpdateD4FromE4()
'
' This sub updates cell D4 based on the selection in cell E4.
'

Sub UpdateD4FromE4()
On Error GoTo ErrorHandler

Dim ParishList As Range
Dim ParishValue As String
Dim TargetCell As Range

Set ParishList = Worksheets("VO Areas").Range("E5:E334")
ParishValue = Range("E4").Value

On Error Resume Next
Set TargetCell = ParishList.Find(ParishValue)
On Error GoTo 0

' Check if the target cell is empty
If TargetCell Is Nothing Then
' Display an error message if the target cell is empty
MsgBox "The target cell is empty. Please select a valid Parish."
Exit Sub
End If

' Update cell D4 based on the selection in cell E4
Range("D4").Value = TargetCell.Value

Exit Sub

ErrorHandler:
Debug.Print "Error in UpdateD4FromE4: " & Err.Description

End Sub

Can you see any errors in this/is this close to achieving what I need?

 

[Monty]

Hello VBXL

My Understanding about your question is to create dependent dropdown lists in Excel based on the choices made in two preceding dropdowns, and you want to ensure that the second dropdown correctly filters data based on both the choices from the first and second dropdowns.

Here's a step-by-step guide on how you can achieve this:

Step 1: Set up your Data
Organize your data in a structured table. For instance:

Column C: First dropdown options (CT, DNN)
Column D: Second dropdown options (Inspector names)
Column E: Data you want to display in E4 based on the selections in C4 and D4
Column F: Data you want to display in F4 based on the selections in C4 and D4
Step 2: Define Named Ranges
Define named ranges for your data. For example:

CT_Inspectors: D5:D169
DNN_Inspectors: D170:D334
CT_Data: E5:F169
DNN_Data: E170:F334
Step 3: Create the First Dropdown
Create the first dropdown in C4 based on your choices (CT, DNN).

Step 4: Create the Second Dropdown
Create the second dropdown in D4. Use data validation, and for the source, use a formula that dynamically changes based on the selection in C4. For example, if your dropdown in C4 is in cell C4, the formula for the source in D4 could be:

=IF(C4="CT", CT_Inspectors, IF(C4="DNN", DNN_Inspectors, ""))

Step 5: Display Data in E4 and F4
In E4 and F4, use formulas to display the data based on the selections in C4 and D4. For example:

=IFERROR(INDEX(CT_Data, MATCH(D4, CT_Inspectors, 0), 1), "")
=IFERROR(INDEX(CT_Data, MATCH(D4, CT_Inspectors, 0), 2), "")

This assumes that you want to display data from the CT_Data range if CT is selected in C4. Adjust the formulas based on your specific requirements.

Repeat a similar process for the DNN case.

Step 6: Test the Dropdowns
Test your dropdowns and data display to ensure everything works as expected.

Remember, these are general instructions, and you might need to adapt them to your specific Excel version and requirements.

 

[VBXL]

Thank you very much for your reply. I'm not sure that's going to work as I won't be searching by Visiting Officer.

It'll either be C4 "Department" and D4 "Parish" (Name) OR C4 "Department" and E4 "Parish" (Number). In addition, if searching from D4 "Parish" (Name), the choice made should then update in E4 to display the correct "Parish" (Number) next to the "Parish" (Name) chosen. Equally, if a choice is made from E4, it should update the "Parish" (Name) in D4.

I hope that makes sense, what do you think?

Test sheet:

Testy.xlsm​

 

[Monty]

Hello VBAXL

Change your search requirement in the code and the code should work!

 

[VBXL]

Thanks for your reply Monty.

Can you confirm that the data is displayed according to your instructions? It's confusing because I had already got all the data in order on the sheet.

 

[VBXL]

Apologies if you haven't had a chance to get back, I'm not sure you saw my question in the last post.

I'm unsure whether what you said concurs with existing data, as I provided a sample sheet with the data laid out as I imagine it needs to be and described the way it needs to work?

Thanks.

 

[VBXL]

Monty, could you please clarify?