0

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").

Improve this question
9
  • Did you create Filter to column for our help or is part of data?
    – Rajesh Sinha
    Jun 7 '18 at 9:40
  • @Rajesh I created a filter to column just for my help. I don't need to show it as a data.
    – Eliya Cohen
    Jun 7 '18 at 10:07
  • But what are the criteria for the filters, if that "Filter to" column doesn't exist?
    – Tom Brunberg
    Jun 7 '18 at 10:15
  • Why don't you use a Pivot Table in sheets 2 and 3? Each Pivot Table would be linked to the data in the Main sheet, and each can have different filters to show the rows you need.
    – piko
    Jun 7 '18 at 10:25
  • Just to be clear, column C in the "Main Sheet" defines the destination sheet for that row ??
    – Gary's Student
    Jun 7 '18 at 12:56
3

With Main Sheet like:

enter image description here

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.

enter image description here

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

Improve this answer
1
  • Thank you. I'll check and post back it out once I come back home.
    – Eliya Cohen
    Jun 7 '18 at 14:15
0

enter image description here

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.

Improve this answer

Your Answer

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

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