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:
In Sheet 2, cell A1 enter:
and in A2 enter:
Copy A2 downwards. In Sheet 2 cell B1 enter:
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.