Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Using Values In A Specific Cell
Is there a way I can use AutoFilter or an Advanced Filter to return values
that are Less Than or Equal To a value that is the result of a formula? Example: I have certain values typed into Sheet 1. On Sheet 2, I pull these values into the row above my column headers. I'd like to filter these columns to show values that are Less Than or Equal To the values from Sheet 1. Currently, I have to manually enter these values into a Custom Filter for each column to display the values and there are about 20-25 columns to filter. I'd like to find an easier way to do this, possibly by creating a macro that can filter based on a cell value, rather than having to manually filter each column for values that are constantly changing. Is it possible? Or do I need to continue doing it manually? Thanks!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Using Values In A Specific Cell
You could use a code line similar to this:
Selection.AutoFilter Field:=1, Criteria1:=Range("D3").Value Do note that you'd still need to run the macro each time the value in "key" cell changes, but by assigning the macro to a shortcut key (or perhaps a Worksheet_Calculate event?) this shouldn't be too much of a hassle. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Sarah" wrote: Is there a way I can use AutoFilter or an Advanced Filter to return values that are Less Than or Equal To a value that is the result of a formula? Example: I have certain values typed into Sheet 1. On Sheet 2, I pull these values into the row above my column headers. I'd like to filter these columns to show values that are Less Than or Equal To the values from Sheet 1. Currently, I have to manually enter these values into a Custom Filter for each column to display the values and there are about 20-25 columns to filter. I'd like to find an easier way to do this, possibly by creating a macro that can filter based on a cell value, rather than having to manually filter each column for values that are constantly changing. Is it possible? Or do I need to continue doing it manually? Thanks!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Using Values In A Specific Cell
Hi Sarah
Take a look at my FastFilter method at http://www.contextures.com/excelfilesRoger.html Hopefully this will help you to understand what I am trying to achieve in the code below Below is a modified version of the code which you can insert in a module and attached to a button on your sheet. I am assuming that you would have formulae in row 1 of your sheet with the list of data, which would have something like ="="&Sheet1!A1 My code assumes there is data in columns 1 to 10, so amend the code as appropriate. Sub FilterList() Dim rownum As Long, colnum As Long, i As Long Dim tblname As String, mylist As Object Dim caret As Long, caret2 As Long Dim crit1 As String, crit2 As String, optype As String, marker As String Dim rng As String 'Change the marker to something other than the caret ^ if required marker = "^" rownum = 1 colnum = 1 On Error Resume Next If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData Range("A1:J1").Interior.ColorIndex = -4142 'clear colour from range GoTo cleanup End If On Error GoTo FilterList_Error If rownum < testrow Then GoTo cleanup For i = 1 To 10 ' amend to suit rng = Cells(1, i).Value If rng < "" Then crit1 = rng caret = InStr(rng, marker) caret2 = InStr(rng, marker & marker) If caret Then crit1 = Trim(Left(rng, caret - 1)) crit2 = WorksheetFunction.Substitute(Mid(rng, caret + 1), marker, "") optype = xlAnd End If If caret2 Then optype = xlOr End If If Val(Application.Version) < 11 Then GoTo earlyversion Set mylist = ActiveSheet.ListObjects If mylist.Count Then ' A List or Table Object is used tblname = mylist(1).Name If Cells(1, i).Value = "" Then ' No filter choice mylist(tblname).Range.AutoFilter Field:=colnum GoTo cleanup ElseIf caret Then mylist(tblname).Range.AutoFilter Field:=i, _ Criteria1:=crit1, Operator:=optype, Criteria2:=crit2 GoTo cleanup Else mylist(tblname).Range.AutoFilter Field:=i, _ Criteria1:=crit1 GoTo cleanup End If ' There is no List object, it is a Range so treat the same as ' earlier versions of Excel End If earlyversion: 'This version of Excel does not support List Objects If Cells(1, i).Value = "" Then Selection.AutoFilter Field:=i ElseIf caret Then Selection.AutoFilter Field:=i, _ Criteria1:=crit1, Operator:=optype, Criteria2:=crit2 Else Selection.AutoFilter Field:=i, Criteria1:=crit1 End If cleanup: 'keep focus on same cell and set colour index if Selection is made Cells(1, i).Activate If ActiveCell < "" Then ActiveCell.Interior.ColorIndex = 40 'change to colour of your choice Else ActiveCell.Interior.ColorIndex = -4142 End If End If Next i exitsub: On Error GoTo 0 Exit Sub FilterList_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Change of VBA Document Sheet4" ActiveCell.Interior.ColorIndex = -4142 Resume exitsub End Sub To install Copy the code above Alt + F11 to invoke the VB Editor InsertModule (or Alt+I+M) Paste the code into the white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macro list Highlight FilterList Run or better still ViewToolbarsFormsselect the buttonClick on your Sheet 2when dialogue appears asking which Macro to attachchoose FilterList The code acts as a Toggle. When first run it filters the list according to your criteria. If run again, when a filter has been applied, it removes all filters to show the whole of your data. -- Regards Roger Govier "Sarah" wrote in message ... Is there a way I can use AutoFilter or an Advanced Filter to return values that are Less Than or Equal To a value that is the result of a formula? Example: I have certain values typed into Sheet 1. On Sheet 2, I pull these values into the row above my column headers. I'd like to filter these columns to show values that are Less Than or Equal To the values from Sheet 1. Currently, I have to manually enter these values into a Custom Filter for each column to display the values and there are about 20-25 columns to filter. I'd like to find an easier way to do this, possibly by creating a macro that can filter based on a cell value, rather than having to manually filter each column for values that are constantly changing. Is it possible? Or do I need to continue doing it manually? Thanks!!! __________ Information from ESET Smart Security, version of virus signature database 4524 (20091019) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4526 (20091020) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Using Values In A Specific Cell
Apologies Sarah
There was one line of superfluous code left in the code example posted, which will stop the code from running. The section which says If rownum < testrow Then GoTo cleanup For i = 1 To 10 ' amend to suit rng = Cells(1, i).Value If rownum < testrow Then GoTo cleanup should be deleted from the code as posted so it just reads For i = 1 To 10 ' amend to suit rng = Cells(1, i).Value -- Regards Roger Govier "Sarah" wrote in message ... Is there a way I can use AutoFilter or an Advanced Filter to return values that are Less Than or Equal To a value that is the result of a formula? Example: I have certain values typed into Sheet 1. On Sheet 2, I pull these values into the row above my column headers. I'd like to filter these columns to show values that are Less Than or Equal To the values from Sheet 1. Currently, I have to manually enter these values into a Custom Filter for each column to display the values and there are about 20-25 columns to filter. I'd like to find an easier way to do this, possibly by creating a macro that can filter based on a cell value, rather than having to manually filter each column for values that are constantly changing. Is it possible? Or do I need to continue doing it manually? Thanks!!! __________ Information from ESET Smart Security, version of virus signature database 4524 (20091019) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4526 (20091020) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering multiple criteria to get specific data | Excel Discussion (Misc queries) | |||
Extract text within two specific values within a cell | Excel Worksheet Functions | |||
FILTERING DATA VALUES | Excel Worksheet Functions | |||
Specific cell values | Excel Discussion (Misc queries) | |||
Count the number of specific values in a cell | Excel Worksheet Functions |