Custom Number Filtering

[leisaellemor]

I am wanting to create a Custom Number Autofilter which draws the information from a cell. Basically from the image below, I want to filter the column by amounts "less than" the amount shown in the cell "e2" (circled in yellow).

To do this, I am using going through the following steps:

1. I have enabled the "filtering" option on the data heading line 

2. I click on the "filter" button and go to "Number Filters" and then "Custom Filters" (for the column shown in purple)

3. I am wanting to select the option of "is less than" and use the number data from a specific cell on the spreadsheet (shown in yellow)

Is it possible to use this filtering option drawing the number from a specific cell, rather than typing in the amount each time? I am hoping to have this function set up as a macro so that I only have the change the amount in the yellow circle each time.

[#2]

@leisaellemor 

Rather than using that tool-bar-based filtering system, learn to use the new FILTER function. With it, you can definitely do what you want to do.

Here's a text based description of how to do it,

and a YouTube video that goes into depth.

[#3]

@mathetes Thankyou. That is part of the problem solved.

However, I don't want the data to be filtered by duplicating into other cells. I am wanting the data to be filtered where it sits originally so that all of the other column data on the same line is still visible. This is the reason I was trying to use the inbuilt filtering as this allows me to keep all of the other column data on the same line.

Any idea if this is possible?

[#4 · Accepted solution]

@leisaellemor To automate this with VBA, open the Visual Basic editor (Alt+F11). In the left-hand pane under VBAProject > Microsoft Excel Objects, double-click the sheet name that contains your data to open the worksheet module. The code will vary slightly, depending on if your data has been formatted as an Excel Table, or if it's just a regular worksheet range.

Version 1: With a Structured Excel Table

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop macro if more than once cell was changed
    If Target.Cells.Count > 1 Then Exit Sub

'Apply filter if the criteria cell was changed
    Dim rg As Range
    Set rg = Me.Range("C1")
    If Not Intersect(Target, rg) Is Nothing Then
        Me.ListObjects(1).Range.AutoFilter Field:=3, criteria1:="<=" & rg.Value
    End If

End Sub

Version 2: With a Worksheet Range

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop macro if more than once cell was changed
    If Target.Cells.Count > 1 Then Exit Sub

'Apply filter if the criteria cell was changed
    Dim rg As Range
    Set rg = Me.Range("C1")
    If Not Intersect(Target, rg) Is Nothing Then
        Dim rgData As Range
        Set rgData = Me.Range("A2").CurrentRegion
        Set rgData = rgData.Offset(1).Resize(rgData.Rows.Count - 1)
        rgData.AutoFilter Field:=3, Criteria1:="<=" & rg.Value
    End If

End Sub

Note: since the screenshot of your data does not reveal the worksheet column letters or rows numbers, I made the assumption that the table begins in cell A2, the column to filter is Field:=3 and the criteria to filter by is in cell C1. Please adjust the range references and field number, if necessary.

Also, Range("A2").CurrentRegion was used here to identify the entire data range. This method will only work properly if your data range does not contain any completely blank rows or columns... the CurrentRegion method identifies all adjacent data in the same manner that pressing Ctrl+Shift+8 (*) will highlight all adjacent data to the active cell. If your data contains additional adjacent cells above the header row, you may need to adjust the Offset and Resize numbers accordingly.

Then, just input a new value in the criteria cell to refresh the filter.

[#5]

@leisaellemor 

Thankyou. That is part of the problem solved.

However, I don't want the data to be filtered by duplicating into other cells. I am wanting the data to be filtered where it sits originally so that all of the other column data on the same line is still visible. This is the reason I was trying to use the inbuilt filtering as this allows me to keep all of the other column data on the same line.

Any idea if this is possible?

A design principle for you, if I might be so bold: separate input or raw data from output.

Or, in other words, don't try to do everything on the same single sheet. Doing this--separating input/raw data from output--would enable you to display or report based on your data in more creative ways. Excel is excellent at this kind of thing, extracting data from a large dataset and producing useful summaries, high level analyses etc. It's a mistake to mix the input and output in the same space. And can lead to unintentional errors. [There have been many people posting their alarm here at having data disappear, only to realize that they'd used that tool bar filter and forgotten having done so.]

The FILTER function enables you to produce a good output page, in the process doing what you want, filtering (to quote) "so that all of the other column data on the same line is still visible, and it can do that cleanly on an output tab of its own.  If you incorporate the SORT function as well, it will still keep all the data on any given line together yet it can also sort for other output (reporting) purposes. That too is explained on the YouTube video my first post linked to.

[#6]

Thank you so much. This is just what I needed.