BarefootPaul
Board Regular
- Joined
- Jul 21, 2011
- Messages
- 54
I have one sheet (Active) that has a table (tblActive) on it and another that I want to use as a dashboard of sorts. On the Dashboard sheet I have a range named "Provider" that has list data validation setup . I'd like a user to be able to select a name from the Provider list and below that return every instance of that from the table in the provider field (Provider1), plus some offset ranges. I have some ideas of how to do it, but am feeling a bit stumped. Here is some of what I imagine I might need and I appreciate any suggestions.
I am hoping it will look something like this after:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Provider[/TD]
[TD]Sam smith[/TD]
[TD]hours[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]Clients[/TD]
[TD]john[/TD]
[TD]3[/TD]
[TD]10/12/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jane[/TD]
[TD]4[/TD]
[TD]11/4/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]david[/TD]
[TD]8[/TD]
[TD]11/19/2012[/TD]
[/TR]
</tbody>[/TABLE]
I am using Excel 2010.
Thanks
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rProvider As Range, rList As Range ', rSearch As Range
Dim ws As Worksheet, wsActive As Worksheet
Dim i As Integer
Dim count As Double
Dim objTable As ListObject
Dim rSearch As ListColumns
Set ws = ActiveWorkbook.Sheets("Dashboard")
Set wsActive = ActiveWorkbook.Sheets("Active")
Set rProvider = ws.Range("Provider")
Set rList = rProvider.Offset(1, 0)
Set objTable = wsActive.ListObjects("tblActive")
Set rSearch = objTable.ListColumns("Provider1").Range 'Fails here
If Target.Row = 14 Then
count = Application.WorksheetFunction.CountIf(rSearch, rProvider) 'Not sure how to do the first arg in vba
For i = 1 To count
Find match 'I'll need to figure out this and am open to suggestions for alternate methods than I have written out here.
rList = match
rList.Offset(0, 1) = match.Offset(0, 4)
rList.Offset(0, 2) = match.Offset(0, 17)
rList = rList.Offset(1, 0)
i = i + 1
Next i
End If
End Sub
I am hoping it will look something like this after:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Provider[/TD]
[TD]Sam smith[/TD]
[TD]hours[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]Clients[/TD]
[TD]john[/TD]
[TD]3[/TD]
[TD]10/12/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jane[/TD]
[TD]4[/TD]
[TD]11/4/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]david[/TD]
[TD]8[/TD]
[TD]11/19/2012[/TD]
[/TR]
</tbody>[/TABLE]
I am using Excel 2010.
Thanks
Last edited: