How can I filter my table from one sheet, into another sheet using formula? without a formula, I can just copy a reference of the cells like so:

=INDEX(MainSheet!A:A,ROW())

And then mark the range as a table, and then filter it by column, and then hide the column for readability. It doesn't look like a solution for me. I want to achieve that without hiding columns/rows in the filtered sheet.

For example, lets say I have 3 sheets:

• Main Sheet
• Sheet 2
• Sheet 3

In my "Main Sheet" sheet, I have:

| ID | Name | Filter To
|-------------------------------
| 1 | Record 1 | Sheet 2
| 2 | Record 2 | Sheet 3
| 3 | Record 3 | Sheet 2
| 4 | Record 4 | Sheet 2
| 5 | Record 5 | Sheet 3

What I want to do next, is to grab these cells, filter them by formula, and then place the filtered rows in the desired sheets ("Sheet 2" and "Sheet 3").

9

2 Answers

With Main Sheet like:

In Sheet 2, cell A1 enter:

=MATCH("Sheet 2",'Main Sheet'!C:C,0)

and in A2 enter:

=IFERROR(MATCH("Sheet 2",INDEX('Main Sheet'!C:C,A1+1):INDEX('Main Sheet'!C:C,9999),0)+A1,"")

Copy A2 downwards. In Sheet 2 cell B1 enter:

=INDEX('Main Sheet'!A:A,$A1)

and copy this both downwards and across.

Note column A identifies the row that the data is filtered from, columns B and C are the Id and Name from that row.

1

Write Following Formula in Sheet2:

In Cell E242 Write this Array Formula, finish with Ctrl+Shift+Enter then fill it down:

{=IFERROR(INDEX(MainSheet!$A$242:$A$246,MATCH(0,IF($F$240=MainSheet!$C$242:$C$246,COUNTIF($E$241:$E241,MainSheet!$A$242:$A$246),""),0)),"")}

In Cell F242 Write this Array Formula, finish with Ctrl+Shift+Enter then fill it down:

{=IFERROR(INDEX(MainSheet$B$242:$B$246, MATCH(0, IF($F$240=MainSheet!$C$242:$C$246, COUNTIF($F$241:$F241, MainSheet!$B$242:$B$246), ""), 0)),"")}

Write Following Formula in Sheet3:

In Cell G242 Write this Array Formula, finish with Ctrl+Shift+Enter then fill it down:

{=IFERROR(INDEX(MainSheet!$A$242:$A$246,MATCH(0,IF($H$240=MainSheet!$C$242:$C$246,COUNTIF($G$241:$G241,MainSheet!$A$242:$A$246),""),0)),"")}

In Cell H242 Write this Array Formula, finish with Ctrl+Shift+Enter then fill it down:

{=IFERROR(INDEX(MainSheet!$B$242:$B$246, MATCH(0, IF($H$240=MainSheet!$C$242:$C$246, COUNTIF($H$241:$H241, MainSheet!$B$242:$B$246), ""), 0)),"")}

N.B. Adjust the Cell address in formula according to your need.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy