1

In Google Sheets, I want to set up a filtered view like this.

Row 1 = column headings  
Row 2 = filter criteria  
Rows 3... = data  

I want the user to type a value into any column of row 2, and this will dynamically update which rows are displayed in the filter view. Is this possible? For example, starting with column C, I enter "Disney" into C2, then I enter this formula as a filter, but it returns only row 2.

=REGEXMATCH(C2, $C$2)

But if I enter it as =REGEXMATCH(C2, "Disney") then it correctly returns all rows with Disney in column C.

Here is an example spreadsheet: https://docs.google.com/spreadsheets/d/1o3cvenwSuimKo9gZuehqo8Y-LEZ_c397JJk7LNmfwQM/edit?usp=sharing

1
  • share a copy of your sheet
    – user0
    Jun 27, 2019 at 19:25

1 Answer 1

2

This won't be possible because you unintentionally create a circular reference by referring $C$2 from inside of the range C1:C. Therefore you will need to put it outside of the box like for example by swapping 1st & 2nd row and creating green filtered view from 2nd row onwards:

0

=REGEXMATCH(C3, $C$1)

0

1
  • This works, thank you! But I have to turn the filter view off and on again to refresh it. Is there some way to make it refresh as soon as I change the C1 cell? I will make the first row all data validation dropdown lists
    – Elliott B
    Oct 8, 2019 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.