Filter one sheet by a range in another sheet

0
votes

I have a Google Spreadsheet document that I'm using to maintain a reference of all business logic on various systems. It is comprised of 2 sheets:

  • Sheet1 is a view of all of the logic. Each row has a unique code column (column B) and many details about the logic being done in other columns
  • Sheet2 is a mapping of the systems to the logic. Each system is on one row. From column E onward, each cell is exactly a code from Sheet1

The relationship between code and system is many to many, so the same code may be used by many systems, and each system may have many codes.

I would like to be able to filter Sheet1 based on whether the code column in each row is found for particular systems.

Example

  • System A and System B are in Sheet2 rows 50 and 51
  • Their codes are from column E to K
  • Filter Sheet1 by code where code is contained in Sheet2!E50:K51. The end result should be Sheet1 shows only those codes (and of course all columns for them)

I have seen and tried a bit of the usual suspects (ARRAY_FORMULA, INDEX, LOOKUP) but I do not yet grok them fully. I thought the answer would be going to "Filter -> By Condition -> Custom Formula is" but I'm not sure what to put there.

Any help is greatly appreciated!

1
google-sheets

1 Answers

2
votes

Short Answer

  1. In custom formulas of filters use INDIRECT to refer to ranges in another sheet.
  2. To test if a value is in a 2D range, compare the value and the range, coerce booleans to numbers and sum them.

Explanation

Part 1: Custom Formulas in filters

Custom formulas in filters and conditional formatting rules can only reference the same sheet, using standard notation (='sheetname'!cell). To reference another sheet in the formula, use the INDIRECT function.

Example

Assuming that the filter criteria are in A2:A3, the filter custom formula in in a sheet called Sheet1 is:

=ISNUMBER(MATCH(A2,INDIRECT("Sheet2!$A$2:$A$3"),0))

Part 2: Test if a value is included in a 2D array

LOOKUP only could look for values in a single column or single row, by the other hand AND and OR functions can't be used in array formulas so, instead of use them we will compare a scalar value with the 2D range. This will return a 2D array of TRUE/FALSE values that we will coerce to number (1 for TRUE, 0 for FALSE) and sum them.

The final custom formula is the following one:

=ArrayFormula(SUM(N(A2=INDIRECT("Sheet2!E50:K51"))))

References