Dropdown Lists based on Matrix Column and Row Headers

AudBall

New Member
Joined
Oct 2, 2015
Messages
23
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2015
Messages
23
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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

New Member
Joined
Oct 2, 2015
Messages
23
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.

VBA 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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,184
Messages
5,629,204
Members
416,372
Latest member
FaitherDave

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
Top