Using Filter function - How do I reference two table column ranges from another workbook?

[ez08mba]

What I'm trying to do: Using the Filter function in one workbook, I'm trying to return results (could be more than one) from a second workbook containing a table. Using the Filter function, I would select one column as the results column, then the second column as the "lookup" equal to a value I designate (e.g., table_range = "my value". So the Filter function operates as:

=FILTER(table_column_values, table_column_lookup = "my value", "No Result")

What I have tried: I'm sure I have missed something obvious, however, I've tried simply selecting the column from the 2nd workbook accepting the result, as well as looked up how to create a reference using brackets ( [ ] ), explanation point ( ! ), apostrophes ( ' ), etc. and can't seem to get it right. I've also tried looking up this scenario for a few hours online. All the examples I have found are hard references to ranges, but nothing using tables.

No matter the attempt, I always get the #value error which is vague in itself.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Will the other workbook be open?

 

[ez08mba]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Will the other workbook be open?

 

[ez08mba]

Thanks for the reminder, I didn't think to update the profile. I'm on 2016 and have a FILTER function that works just like the online / 365 version. However, I'm not concerned with the function, I'm having trouble with the table references. I can't even seem to get the table references to work within the same spreadsheet. I'm sure it is something and I'm just not seeing it. When I type the table name it comes up in the list of options, but after that I'm drawing a blank. In the meantime, I'm back trying to relearn what the syntax is in manually entering a table reference.

 

[Fluff]

If you have 2016, what is the Filter function you are using?

 

[ez08mba]

Sorry for the delayed reply....

An Microsoft.com MVP suggested the following code, however, I'm thinking to return to the use of existing functions to do the same thing. So I'm looking at alternatives. In essence, I am attempting to go a step beyond the VLookup and return multiple results. In the end, I was really hoping, and it seemed logical to assume, someone had already solved this problem with older versions of Excel... and I would prefer a UDF vs a lengthy formula.

However, my real issue was I couldn't seem to reference to the table range successfully. I'm not a total idiot though... ... as a test, I can reference a separate workbook table successfully with VLookup (e.g., =VLOOKUP(M7,'inbound-channels.xlsx'!Inbound[#Data],2,0) )... I'm just wanting to take it to the next level as previously stated.

Function Source

Function FILTER_AK(Where, Criteria, Optional If_Empty) As Variant
  Dim Data, Result
  Dim i As Long, j As Long, k As Long
  'Create space for the output (same size as input cells)
  With Application.Caller
    i = .Rows.Count
    j = .Columns.Count
  End With
  'Clear
  ReDim Result(1 To i, 1 To j)
  For i = 1 To UBound(Result)
    For j = 1 To UBound(Result, 2)
      Result(i, j) = ""
    Next
  Next
  'Count the rows to show
  For i = 1 To UBound(Criteria)
    If Criteria(i, 1) Then j = j + 1
  Next
  'Empty?
  If j < 1 Then
    If IsMissing(If_Empty) Then
      Result(1, 1) = CVErr(xlErrNull)
    Else
      Result(1, 1) = If_Empty
    End If
    GoTo ExitPoint
  End If
  'Get all data
  Data = Where.Value
  'Copy the rows to show
  For i = 1 To UBound(Data)
    If Criteria(i, 1) Then
      k = k + 1
      For j = 1 To UBound(Data, 2)
        Result(k, j) = Data(i, j)
      Next
    End If
  Next
  'Return the result
ExitPoint:
  FILTER_AK = Result
End Function

 

[Fluff]

Without really knowing anything about that code, I'm afraid I cannot really help.

 

[ez08mba]

Without really knowing anything about that code, I'm afraid I cannot really help.

No problem! I always manage to solve my own problem anyhow. Thanks for the willingness to assist.

 

[Fluff]

Sorry I couldn't have been more help.