Excel – Filter table from one sheet to another using formula

microsoft excelmicrosoft-excel-2013

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

Best Answer

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.

Related Question