Lookup Column & Count Values in that Column

[kromer12]

Hello All,

I have a drop down that lists the column headers on a sheet "MyData". I need to use the value in the drop down to find the column on sheet "MyData" and then use a countifs() function in that column.

I am able to find the column number using this:
E4 - Drop down value
MyData!$A$1:$HD$1 - Data headers
=MATCH(E4,MyData!$A$1:$HD$1,0)

I am able to perform my countifs() using a static column (picking column "MyData!E:E") using this:
C9 & B9 are both variable criteria for my countifs()
=COUNTIFS(MyData!E:E,">0",MyData!D:D,"<6",MyData!B:B,C9,MyData!A:A,B9)

I would like to use a formula (or however many needed) if at all possible to easily toggle between different drop down values.

Any help is appreciated!

Thanks,

Sean

 

[Marcelo Branco]

Maybe something like this

=COUNTIF(INDEX(MyData!$A:$HD,0,MATCH(E4,MyData!$A$1:$HD$1,0)),">0")

It's a very simple example working with just one column, but it can be adapted to multiple columns (COUNTIFS) dealing with MATCH(...).
Assuming MATCH(E4,MyData!$A$1:$HD$1,0) returns 5 (column E), so, for example, to get column D try
MATCH(E4,MyData!$A$1:$HD$1,0)-1

Hope this helps

M.

 

[kromer12]

Works great! Thank you!

I also found something else that works:

=COUNTIF(INDIRECT("MyData!"&$E$5),">0")

Where E5 = "E:E" determined by the following code when E4 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$4" Then
Dim myE As String, Col As String, myCell As Long
Set MDetail = Worksheets("Month Detail")
Set MyData = Worksheets("MyData")
myCell = MyData.Range("A1:HD1").Find(MDetail.Range("E4").Value).Column
Col = Split(MyData.Cells(1, myCell).Address(1, 0), "$")(0)
MDetail.Range("E5").Value = Col & "2:" & Col & "1000"

End If
End Sub

In case anyone is interested in alternatives

Thanks,

Sean