Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.

  • What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)? – jehad Jul 15 '16 at 1:12
  • Please Edit your post & add the Criteria to Sort & Filter like single or multiple Criteria, better add some sample data. – Rajesh S Sep 15 '19 at 13:01
  • Continue,, if you want to automate the method to Sort then Filter,,, VBA macro would be a good option,, are you comfortable with Macro?,, Edit post & add TAG VBA. – Rajesh S Sep 15 '19 at 13:05

Another method using a data table

Using same Sheet1 Data

sheet1 data

On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.

Select cell A5 and A6 and drag across the required number of columns

and edit text to your column headings

Sheet3 Headings

In cell A6 add the following formula


and drag A6 across required columns then down required rows

enter image description here

then use filters as required.


Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:

From data in my sheet1 I can filter for data with specific text in a column


on Sheet2 add headings and type the text you wish to filter for in B1.

Sheet2 headings

In A5 add the following array formula

=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")

Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}

Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.

Ammend the formulas to increment the index column number.

Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.

B5 should now show index column number 2

{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}

and C5 with column index 3 as follows

{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}

Drag these formulas down for your expected maximum number of data rows

Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.

Sheet2 completed

This has tested ok for Excel 2010

You can automate this further by adding a Data Validation List for cell B1.

  • This one is better than previous answer,, or you may combine both as Method 1 & 2,,, but you have missed one issue SORT then Filter !! – Rajesh S Sep 15 '19 at 13:00

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.