Reference and filter data to other sheet in Excel

Is it possible to reference data from one Excel spreadsheet to another spreadsheet and filter it? The data on the new sheet should be updated automatically. E.g.

Sheet 1

colA|colB|colC

xxxx|1111|2222

yyyy|3333|4444

xxxx|5555|6666

filter by xxxx:

Sheet 2

colA|colB|colC

xxxx|1111|2222

xxxx开发者_如何学Go|5555|6666

Sheet 2 should be automatically updated if data on Sheet 1 changes

---

This works in Excel 2010:

• Insert > Pivot Table
• Add colA, colB, and colC as Row Labels
• PivotTable Design ribbon bar (Excel 2010)
• Subtotals > Do not show
• Grand Totals > Off for rows and columns
• Report Layout > Show in tabular form
• Report Layout > Repeat all item labels
• PivotTable Options > deselect the +/- buttons
• You should now essentially have a copy of your table
• Filter by whichever field you wish

This gets you close to what you want. You will still need to refresh the PivotTable (Ctrl + Alt + F5 to refresh all in the workbook).

---

This is why pivot tables exist. It will do this automatically for you.