jrichall - This answer is to provide a framework, with examples, to help address your problem. It does not lay things out exactly as you have designed.
I've broken it down this way ...
- Lists are required for unique Names, Status, Idea Numbers, etc., that exist on the AllIdeas table. These lists are used to limit the end users choices for filtering, but they need to be kept up to date as/when the contents change.
- You are limiting the end user to one kind of filter at a time - either by Name, Status, Idea Number, or something else. This means you need a way to eliminate one kind of filter when the other is chosen.
- The old filtering of AllIdeas needs to be eliminated before a new filter is applied.
- Displaying the filtered results on the Dashboard means maintaining the dashboard appearance.
Note: In my example, I am not using Combo Boxes. However, the concepts are easily transportable.
A simple AllIdeas
To test the code, a simple mockup of AllIdeas was generated ...
A simple dashboard
A simple dashboard was also put together. In it, Cells A2, B2, and C2 have their input protected using Data Validation.
A named Range defines the valid data. Illustrated above is the named Range "Names".
Lists and maintaining them
The Lists of valid Names, Status, and Numbers (named ranges) are maintained on a tab named "DropDowns". It looks like the following ...
You can see these lists do not contain all of the information contained in the AllIdeas table. Below is VBA code to update the "Names" list. Similar ones exist for updating the "Status" list and the "Numbers" list.
Sub UpdateNamesList()
Dim IdeaSht As Worksheet, ListSht As Worksheet
Dim IdeaRng As Range, myRng As Range
Dim iCount As Long, NameCol As Long
Dim myDict As Object, myKey As Variant
Dim namedRange As Name
' Initial
Set IdeaSht = Worksheets("AllIdeas")
Set ListSht = Worksheets("DropDowns")
Set myDict = CreateObject("Scripting.Dictionary")
' Find the column with the user names
For Each myRng In IdeaSht.Range(IdeaSht.Cells(1, 1), IdeaSht.Cells(1, IdeaSht.Cells(1, IdeaSht.Columns.Count).End(xlToLeft).Column))
If myRng.Value = "Idea Owner" Then
NameCol = myRng.Column
Exit For
End If
Next myRng
' Pull out unique user names
For Each myRng In IdeaSht.Range(IdeaSht.Cells(2, NameCol), IdeaSht.Cells(IdeaSht.Range("A" & IdeaSht.Rows.Count).End(xlUp).Row, NameCol))
If Not myDict.exists(myRng.Value) Then
myDict.Add myRng.Value, myRng.Value
End If
Next myRng
' Change "Names" list to contain the unique user names
For Each myRng In ListSht.Range(ListSht.Cells(1, 1), ListSht.Cells(1, ListSht.Cells(1, ListSht.Columns.Count).End(xlToLeft).Column))
If myRng.Value = "Names" Then
NameCol = myRng.Column
Exit For
End If
Next myRng
iCount = 0
For Each myKey In myDict
ListSht.Cells(2 + iCount, NameCol).Value = myKey
iCount = iCount + 1
Next myKey
Set namedRange = ActiveWorkbook.Names("Names")
namedRange.RefersTo = ListSht.Range(ListSht.Cells(2, NameCol), ListSht.Cells(1 + iCount, NameCol))
' clean up
Set IdeaSht = Nothing
Set ListSht = Nothing
Set myDict = Nothing
Set namedRange = Nothing
End Sub
After running these routines, the named range lists now look like the following ...
These routines are added to the WorkBook_Open Event code, so they stay up to date for the user ...
Private Sub Workbook_Open()
UpdateNamesList
UpdateStatusList
UpdateNumberList
End Sub
Now, the user has drop down lists that are up to date (a similar method could be used to keep combo boxes up to date) ...
Filtering - there can be only one!
To manage clearing filtering in Cell A2 when something is specified in Cell B2, or all other combinations of changes in the three filter specifications, the WorkSheet_Change event code for the Dashboard was used ...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iLoop As Long
If Intersect(Target, ActiveSheet.Range("A2:C2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For iLoop = 1 To 3
If Target.Column <> iLoop Then ActiveSheet.Cells(2, iLoop).Value = ""
Next iLoop
Application.EnableEvents = True
End Sub
Now, picking one filter automatically clears the other ...
Filtering and Displaying
The "FetchIdeas" button is connected to the following piece of VBA code ...
Sub FetchAllIdeas()
Dim IdeaSht As Worksheet, DshbrdSht As Worksheet
Dim myRng As Range
Dim lstRow As Long, lstCol As Long
Dim FltrVal() As Variant, FltrCol As Long
Dim myField As Long, iLoop As Long
'Initial
Set IdeaSht = Worksheets("AllIdeas")
Set DshbrdSht = Worksheets("Dashboard")
'Determine which filter we are using
ReDim FltrVal(1 To 1)
myField = 0
For Each myRng In DshbrdSht.Range("A2:C2")
If myRng.Value <> "" Then
FltrVal(1) = myRng.Value
If myRng.Offset(-1, 0).Value = "GetByName" Then myField = 2
If myRng.Offset(-1, 0).Value = "GetByStatus" Then myField = 3
If myRng.Offset(-1, 0).Value = "GetByNumber" Then myField = 1
Exit For
End If
Next myRng
'Clear the dashboard
lstRow = DshbrdSht.Range("A" & DshbrdSht.Rows.Count).End(xlUp).Row
For iLoop = lstRow To 5 Step -1
DshbrdSht.Cells(iLoop, 1).EntireRow.Delete
Next iLoop
'Filter the AllIdeas tab
If myField > 0 Then
lstRow = IdeaSht.Range("A" & IdeaSht.Rows.Count).End(xlUp).Row
lstCol = IdeaSht.Cells(1, IdeaSht.Columns.Count).End(xlToLeft).Column
With IdeaSht
.Cells.AutoFilter
With .Range(IdeaSht.Cells(1, 1), IdeaSht.Cells(lstRow, lstCol))
.AutoFilter field:=myField, Criteria1:=FltrVal
' and display on the dashboard
.SpecialCells(xlCellTypeVisible).Copy Destination:=DshbrdSht.Range("A5")
End With
End With
End If
End Sub
It applies the filters, clears the dashboard, and puts the new filterd data on the dashboard ...
=IFERROR(INDEX(AllIdeas!D:D,MATCH(Dashboard!D13,AllIdeas!B:B,0)),"")- I'm running an index match, so a user can see the idea description, solution, and status; so long as they know the idea number. I posted an example of my dashboard at the end of my initial question. - jrichall