Filter Worksheet by value in separate Worksheet

A

achilles18

New Member
Joined
May 24, 2015
Messages
20
Hi Guys,

I tried to search these forums and Google for an answer but I could not find anything.

What I am trying to do is filter a worksheet by a value from the cell in another.

So I have two worksheets one is called "Reports" and one is called "Setup Data".
The Reports worksheet will be the main sheet which will display graphs and summarized data.
The Setup Data worksheet has all the data that the graphs and stuff are/will be built on. it does have a header row and I have done an autofilter so within the setup data worksheet I can filter fine.

Now what I want to happen is when I select/input a value in cell (K2) within the Reports worksheet it should filter the Setup Data worksheet and then my graphs and so forth on the Reports worksheet will then update.

Any help would be greatly appreciated.

Regards
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
How big is your database that you are basing this off of? You could do this with a bunch of index(match formulas, or it can be done with VBA. If your database is not that large and your expected number of records is not that large, then maybe index(match would work. If the expected number of records is large, then VBA is the better solution. Is VBA an option for you?
 
Upvote 0
mick0005 said:
How big is your database that you are basing this off of? You could do this with a bunch of index(match formulas, or it can be done with VBA. If your database is not that large and your expected number of records is not that large, then maybe index(match would work. If the expected number of records is large, then VBA is the better solution. Is VBA an option for you?
Click to expand...

G'day Mick0005 thanks for getting back to me. Currently the data isn't very large wouldn't even be 40 rows but it does have the capacity to grow quite big over time. I have no problem with using VBA!

Thanks!
 
Upvote 0
Can you post your workbook using dropbox or some other hosting service, or alternatively, give me a sample of your data by using the html maker in my signature?
 
Upvote 0
Here is a possible solution. I don't know your familiarity with VBA, but I made some comments so you can easily modify the constants if you need to. This particular solution relies on your Setup Data to be in Table format.

https://www.dropbox.com/s/pdbtcpltuzo2nxu/Filter based on value in a cell.xlsm?dl=0

Let me know if you have questions on how to use this/how to modify the VBA if you need to.

This code goes into a new standard module:
Code: 
Sub Filter()

'This macro will filter data within a table based on the value input in a cell
    
Const wsName As String = "Setup Data"    ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "Table1"    ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Field1"   ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K2")   ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports"   'change this constant (in quotes) to the name of the sheet where your filter cell resides
   
Dim FilterCriteria
   
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
   
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
   
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
   
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
   


    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
     
     'Get the filter's criteria from the user.  If you want the filter to be wildcard based, you can remove the comment mark ' from
     'the 3 indented lines below.
    FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
        'If FilterCriteria = "" Then Exit Sub
        'FilterCriteria = Replace(FilterCriteria, "*", "")
        'FilterCriteria = "*" & FilterCriteria & "*"
     
     'Filter the data based on the user's input
     tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
     
     'Scroll up to the top so user more easily sees the filtered data
    ActiveWindow.SmallScroll Down:=-30000
End Sub

Right click on the "Reports" tab/worksheet tab and put this code inside of the worksheet module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will cause an action (call filter macro) when they are changed.
        Set KeyCells = Range("K2") 'modify this range with the cell location of the report filter
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        ' Action to occur when the cell is changed
        Call Filter
       
    End If
End Sub
 
Upvote 0
Here you go... note that I needed to override your duration values for line 2 and line 3 so I could test that the graph was changing. You had the times identical for all 3 lines so if you changed the product line the graph would appear static.

Are you trying to have this work for the product selection as well? Also, keep in mind that right now you have your range fixed for the chart (11 rows at the moment). If you were to filter by a line and that line had more than 11 records in it, your chart won't dynamically expand to do that. Dynamically expanding charts can be accomplished using OFFSET function, but you should search on that first in the forum because it has been covered plenty of times before.

https://www.dropbox.com/s/75wygqbgyggpqwn/Reports.xlsm?dl=0
 
Last edited:
Upvote 0
mick0005 said:
Here you go... note that I needed to override your duration values for line 2 and line 3 so I could test that the graph was changing. You had the times identical for all 3 lines so if you changed the product line the graph would appear static.

Are you trying to have this work for the product selection as well? Also, keep in mind that right now you have your range fixed for the chart (11 rows at the moment). If you were to filter by a line and that line had more than 11 records in it, your chart won't dynamically expand to do that. Dynamically expanding charts can be accomplished using OFFSET function, but you should search on that first in the forum because it has been covered plenty of times before.

https://www.dropbox.com/s/75wygqbgyggpqwn/Reports.xlsm?dl=0
Click to expand...

Hi mick0005 thanks so much for your reply and the awesome work you have done! yes I would like to include a sort by product as well. The data that was in there was dummy data that I was testing with. Eventually I will be bringing data in via ODBC from sql but didn't want to do that until I had the solution built.

Ok I will definitely look into the Dynamically expanding chart using the offset function.

Thanks again for all your help! greatly appreciated!!
 
Upvote 0
Assumes that the product filter would be in cell K3 on the Reports worksheet.

Change the code in the worksheet module of the reports worksheet to:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will cause an action (call filter macro) when they are changed.
        Set KeyCells = Range("K2:K3") 'modify this range with the cell location of the report filter
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        ' Action to occur when the cell is changed
        Call FilterLine
        Call FilterProduct
       
    End If
End Sub

Change the code for the standard module to:

Code: 
Sub FilterLine()

'This macro will filter data within a table based on the value input in a cell
    
Const wsName As String = "Setup Data"    ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL"    ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Prod Line"   ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K2")   ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports"   'change this constant (in quotes) to the name of the sheet where your filter cell resides
   
Dim FilterCriteria
   
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
   
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
   
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
   
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
   


    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
     
     'Get the filter's criteria from the user.  If you want the filter to be wildcard based, you can remove the comment mark ' from
     'the 3 indented lines below.
    FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
        'If FilterCriteria = "" Then Exit Sub
        'FilterCriteria = Replace(FilterCriteria, "*", "")
        'FilterCriteria = "*" & FilterCriteria & "*"
     
     'Filter the data based on the user's input
     tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
     
     'Scroll up to the top so user more easily sees the filtered data
    ActiveWindow.SmallScroll Down:=-30000
End Sub




Sub FilterProduct()


'This macro will filter data within a table based on the value input in a cell
    
Const wsName As String = "Setup Data"    ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL"    ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Product"   ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K3")   ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports"   'change this constant (in quotes) to the name of the sheet where your filter cell resides
   
Dim FilterCriteria
   
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
   
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
   
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
   
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
   


    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
     
     'Get the filter's criteria from the user.  If you want the filter to be wildcard based, you can remove the comment mark ' from
     'the 3 indented lines below.
    FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
        'If FilterCriteria = "" Then Exit Sub
        'FilterCriteria = Replace(FilterCriteria, "*", "")
        'FilterCriteria = "*" & FilterCriteria & "*"
     
     'Filter the data based on the user's input
     tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
     
     'Scroll up to the top so user more easily sees the filtered data
    ActiveWindow.SmallScroll Down:=-30000
End Sub
 
Upvote 0
mick0005 said:
Assumes that the product filter would be in cell K3 on the Reports worksheet.

Change the code in the worksheet module of the reports worksheet to:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will cause an action (call filter macro) when they are changed.
        Set KeyCells = Range("K2:K3") 'modify this range with the cell location of the report filter
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        ' Action to occur when the cell is changed
        Call FilterLine
        Call FilterProduct
       
    End If
End Sub

Change the code for the standard module to:

Code: 
Sub FilterLine()

'This macro will filter data within a table based on the value input in a cell
    
Const wsName As String = "Setup Data"    ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL"    ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Prod Line"   ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K2")   ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports"   'change this constant (in quotes) to the name of the sheet where your filter cell resides
   
Dim FilterCriteria
   
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
   
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
   
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
   
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
   


    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
     
     'Get the filter's criteria from the user.  If you want the filter to be wildcard based, you can remove the comment mark ' from
     'the 3 indented lines below.
    FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
        'If FilterCriteria = "" Then Exit Sub
        'FilterCriteria = Replace(FilterCriteria, "*", "")
        'FilterCriteria = "*" & FilterCriteria & "*"
     
     'Filter the data based on the user's input
     tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
     
     'Scroll up to the top so user more easily sees the filtered data
    ActiveWindow.SmallScroll Down:=-30000
End Sub




Sub FilterProduct()


'This macro will filter data within a table based on the value input in a cell
    
Const wsName As String = "Setup Data"    ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL"    ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Product"   ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K3")   ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports"   'change this constant (in quotes) to the name of the sheet where your filter cell resides
   
Dim FilterCriteria
   
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
   
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
   
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
   
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
   


    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
     
     'Get the filter's criteria from the user.  If you want the filter to be wildcard based, you can remove the comment mark ' from
     'the 3 indented lines below.
    FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
        'If FilterCriteria = "" Then Exit Sub
        'FilterCriteria = Replace(FilterCriteria, "*", "")
        'FilterCriteria = "*" & FilterCriteria & "*"
     
     'Filter the data based on the user's input
     tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
     
     'Scroll up to the top so user more easily sees the filtered data
    ActiveWindow.SmallScroll Down:=-30000
End Sub
Click to expand...

Wow Mick0005 thank you so much!! That is so much work I don't know how to thank you enough! I never expected you to go too such lengths!

Thank you again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
filter loop
Replies
2
Views
171
Joe4
Joe4
sanantonio
  • Question
VBA, filtering pivot table based on cell value
Replies
3
Views
465
Alex Blakenburg
A
J
  • Solved
Filter date time data so I only get one data point per hour
2 3
Replies
24
Views
461
Peter_SSs
Peter_SSs
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
444
Alex Blakenburg
A
Q
  • Question
Automate missing cell value report
Replies
2
Views
111
mumps
mumps

Forum statistics

Threads
1,214,585
Messages
6,120,388
Members
448,957
Latest member
Hat4Life

Share this page

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
Back
Top