Dropdown Lists based on Matrix Column and Row Headers

[AudBall]

I have a matrix A1:AY51. B1:AY1 has headings. A2:A51 has headings.

I'm trying to see if there's a way to have a drop down for all the cells in the matrix dependent on the respective row and column header.

For example: Cell B3 could be selected as either B1 or A3, Cell C4 could be either C1 or A4, etc.

I have done some research, but haven't come across any formula or VBA that would facilitate this.

Thank you in advance for any assistance.

 

[Akuini]

I don't quite understand what you want, could you explain in more detail?

Cell B3 could be selected as either B1 or A3

Do you mean in cell B3 the data validation will have the value of B1 and A3 as its list?

 

[AudBall]

I don't quite understand what you want, could you explain in more detail?



Do you mean in cell B3 the data validation will have the value of B1 and A3 as its list?

Yes. That's correct. Think of it like a decision tree, but in a matrix format. Each header for the x and y-axis would be the data validation list for the corresponding xy cell.

 

[Akuini]

Ok, try this:
1. In Range("B2:AY51") insert data validation, choose List, you can use cell A1 as the source.
2. The code below is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:AY51")) Is Nothing Then
        Target.Validation.Modify Formula1:=Cells(1, Target.Column) & "," & Cells(Target.Row, 1)
    End If

End Sub

 

[AudBall]

Ok, try this:
1. In Range("B2:AY51") insert data validation, choose List, you can use cell A1 as the source.
2. The code below is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:AY51")) Is Nothing Then
        Target.Validation.Modify Formula1:=Cells(1, Target.Column) & "," & Cells(Target.Row, 1)
    End If

End Sub

Finally got a chance to sit down and try this. It works perfectly!! Thank you!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.