1

I want to display all rows that have a "Gender" column equal to "M" inside "sheet2", where the data is retrieved from "sheet1". The filtered data also has to be real time, meaning whenever I make a change in the Gender column in "sheet1", "sheet2"'s data should also change accordingly.

How could I achieve this?

Sheet 1

sheet 1 image link

Sheet 2 (empty)

sheet 2 image link

Improve this question

3 Answers 3

Reset to default
6

Try to use Power Query to get the result:

  1. Select Range- go to Data- select From Range/Table- Enter Power Query Editor: enter image description here

  2. Filter Gender Column- Close and Load to New WorkSheet: enter image description here

If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.

Improve this answer
2
  • is there a way to update the information in the filtered view (Sheet 2) and it gets updated in the master data source (Sheet1). I tested in the way you mentioned, but I cannot in the other direction.
    – David Leal
    Commented Sep 19, 2019 at 5:06
  • I appreciate that this answer probably answers my question, however I find it very difficult to follow the animations - too fast, no way to pause. Also does not help someone relying on a screen reader.
    – Tim Holt
    Commented Jun 24, 2022 at 16:54
2

To solve the issue you need to create a Helper Column in Sheet 1.

enter image description here

Formula in Cell D2 & fill it Down.

=IF(C2="M",1+MAX($D$1:D1),"")

Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")

How it works:

This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.

Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.

enter image description here

Note:

  1. You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
  2. Formula in Sheet 2 will reflect modifications in Gender also.

enter image description here

  1. Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.
  2. Adjust Cell references in the Formula as needed.
Improve this answer
0

I've added this answer in regard of New Arrival Dynamic Array Formula, works only with Version 2021 and higher.

enter image description here

How it works:

  1. Convert range A51:C57 into TABLE.

  2. Reason is, adding new record expands the data set automatically, and if match with the criterion updates the FILTERED records on other sheet.

  3. Formula used in Sheet 4 cell A3 is,

    =FILTER(Sheet3!$A$51:$C$60,Sheet3!$C$51:$C$60="M","")

N.B.

  1. The biggest advantage of using this NEW dynamic array formula are,

    A. Spills automatically when SOURCE data receives NEW Records.

    B. Considering the OLD solutions, no need of HELP data.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .