# Dropdown Lists based on Matrix Column and Row Headers

#### AudBall

##### New Member

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.

### 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
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
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
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
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
You're welcome, glad to help, & thanks for the feedback.

Replies
12
Views
265
Replies
1
Views
61
Replies
4
Views
145
Replies
6
Views
436
Replies
7
Views
169

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.

### Which adblocker are you using?

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

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