Table Automatically Filters Entries to New Table Based on Criteria

I have a master list of chemical result data. It contains columns for productID, fat%, protein%, salt content, etc. and then a final column called "COA Acceptable?" that will contain 'Yes' or 'No'.
I need the table to automatically filter all data with a 'Yes' in the "COA Acceptable" column to a new table.

The problem is that the unfiltered raw table (call it the Master List) is updated daily and will constantly have new entries added. Therefore, I need a way to automatically filter the data to the COA Acceptable table, without having to Advanced Filter the data every time I add a new entry. I tried to record a Macro to auto-filter the data, but it is not working.

Is there a way to accomplish this?

You could use a helper column next to the COA Acceptable? column with a formula which identifies "Yes" records and allocates a unique sequential number to them. This can be copied down beyond your current data, in order to accept new data that is added.

Then you can use a simple INDEX/MATCH formula to fill your second table with the appropriate records.

I can show you how to do this if you attach a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

I have attached an example

The workbook should be attached.

Put this formula in cell P2 of Sheet1:

=IF(O2="Yes",MAX(P$1:P1)+1,"-")

then copy down to beyond the bottom of your data in order to accommodate new data being added. This allocates a unique sequential number to each record that has Yes in column O.

Then you can use this formula in P2 of Sheet2:

=IFERROR(MATCH(ROWS($1:1),Sheet1!P:P,0),"")

which when copied down finds the rows where the first, second, third etc. matching record exists on Sheet1.

Then you can put this formula in cell A2 of Sheet2:

=IF($P2="","",INDEX(Sheet1!A:A,$P2))

which can be copied across to bring the appropriate data across for that matching record.

Finally, you can copy the formulae in row 2 down as far as you need them. The attached file shows this in operation.

Hope this helps.

Pete