Filtering Using Values In A Specific Cell

S

Sarah

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!!!
 
L

Luke M

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.
 
R

Roger Govier

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
Insert>Module (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
View>Toolbars>Forms>select the button>Click on your Sheet 2>when dialogue
appears asking which Macro to attach>choose 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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4526 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
R

Roger Govier

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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4526 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting and filtering text 0
Dates In Excel 5
Setting column width in a table with a macro is not working 0
need help on either doing a loop and counting specific values orusing subtotal function 2
Calculate the median value before a specified list 0
Newbie Help - IF Yes No Function 0
Word macro that can call cells out of Excel for a find and replace 0
Solution to tricky problem of showing exactly Top N rows in Pivottable 0

Top