How to ignore filtered data in Excel formula

Question

I am using Index / Match to retrieve data from a linked table to populate in the first table. In my linked table, I filtered the values, but the filtered values ​​are still populated in my first table. If Index / Match is not smart enough to capture only filtered values, how can I get around this (preferred formula but acceptable for VBA) to get only filtered values.

Here is my current formula:

=INDEX(Table_owssvr__1[MyValues],MATCH([@[ID]],Table_owssvr__1[ID],0))

Answer 1

I managed to get this to work as follows:

1) Create three worksheets: one for customers, one for purchases and one for purchases for the client.

2) , :

Sub Purchases()
Dim Rng As Range
Set Rng = Worksheets("Comments").Columns("A")
Set Rng = Rng.Resize(65535, 1).Offset(1, 0)
Set Rng = Rng.Resize(, 5).SpecialCells(xlCellTypeVisible)
Rng.Copy Worksheets("PurchasesforClient").Range("A2")
End Sub

3) , 2, . , . VBA, , B23 - , , :

Public CurrentValue As Double

Private Sub Worksheet_Activate()
CurrentValue = Application.WorksheetFunction.Sum(ActiveSheet.Range("B23"))
End Sub

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.Sum(Range("B23")) <> CurrentValue Then Purchases
End Sub

4) purchaseforclient index/match . , ..

Answer 2

SUBTOTAL, . ( SUBTOTAL)

, , , .

, VBA, , . , .

, , , . (, sumFilteredColumn - !)

Public Function sumFilteredColumn(startCell As Range)

    Dim lastRow As Long ' the last row of the worksheet which startCell is on
    Dim currentCell As Range
    Dim runningTotal As Long ' keeps track of the sum so far

    lastRow = lastRowOnSheet(startCell)
    Set currentCell = startCell

    ' Loop until the last row of the worksheet
    Do While currentCell.Row <= lastRow
        ' Check currentCell is not hidden
        If Not cellIsOnHiddenRow(currentCell) Then
            ' -------------------------------------------------
            ' Here where the magic happens. Change this to
            ' change sum to, e.g. concatenate or multiply etc.
            If IsNumeric(currentCell.Value) Then
                runningTotal = runningTotal + currentCell.Value
            End If
            ' -------------------------------------------------
        End If
        Set currentCell = currentCell.Offset(1) ' Move current cell down
    Loop

    sumFilteredColumn = runningTotal

End Function

' return the number of the last row in the UsedRange
' of the sheet referenceRange appears in
Public Function lastRowOnSheet(referenceRange As Range) As Long

    Dim referenceSheet As Worksheet
    Dim referenceUsedRange As Range
    Dim usedRangeCellCount As Long
    Dim lastCell As Range

    Set referenceSheet = referenceRange.Parent
    Set referenceUsedRange = referenceSheet.usedRange
    usedRangeCellCount = referenceUsedRange.Cells.CountLarge

    Set lastCell = referenceUsedRange(usedRangeCellCount)
    lastRowOnSheet = lastCell.Row

End Function

' Is the row which referenceCell is on hidden by a filter?
Public Function cellIsOnHiddenRow(referenceCell As Range) As Boolean

    Dim referenceSheet As Worksheet
    Dim rowNumber As Long

    Set referenceSheet = referenceCell.Parent
    rowNumber = referenceCell.Row

    cellIsOnHiddenRow = referenceSheet.Rows(rowNumber).EntireRow.Hidden

End Function

Answer 3

LondonRob SUBTOTAL. AGGREGATE , SUBTOTAL, , ( ). VBA, .

.

Answer 4

...

" " , ...

=INDEX(ARRAY.FILTER(Table_owssvr__1[MyValues]),MATCH([@[ID]],ARRAY.FILTER(Table_owssvr__1[ID]),0))

ARRAY.FILTER() function " (, ) ."

MOREFUNC *

---

MOREFUNC ADDON

• Morefunc Addon is a free library of 66 new worksheet features.
• HERE is some information (according to the original author)
• here is the last working download link I found
• Here is a good installation pass-through video