Reference and filter data to other sheet in Excel

Question

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|5555|6666

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

Answer 1

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

Answer 2

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