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

[TheJay]

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

drive.google.com

 

[kevin9999]

Is this possible?

I don't think so, but I'll happily be corrected

 

[Armstrong_N14]

I don't think so, but I'll happily be corrected

I agree.. you're trying to override cells with formula and reuse them and vice versa..

maybe a macro will do the trick but not 100% sure

 

[TheJay]

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

' 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?

 

[TheJay]

Anyone?

 

[TheJay]

Anyone?

 

[TheJay]

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