Advanced filter v Query

[Gooford]

Hi,

I have a spread-sheet with a wide range of fields that I need to set up to filter to separate tabs based on a specified criteria.

I'm not sure what is the best way to do this, I can see from looking online that one way is to use the advanced filter function and another option might be a query. I would really welcome some advice on the best way to proceed.

If possible I would like to set it up so that the filter works automatically (doesn't need reseting each time).

Please find attached a simplified example of what I am trying to achieve. Table 1 represents the full data set and table 2 and table 3 represent examples of what I would like to be able to do. Table 3 is slightly different as the columns are not adjacent, not sure if that would require a different approach?

Any help would be great, please let me know if I haven't explained myself clearly.

Regards

Matt

[Norie]

Matt

Can you explain further exactly what you want to do, especially the 'automatically' part?

[Gooford]

Hi,

Thanks for getting back.

I can see that I could filter all the records which are "Yellow" to another sheet using Advance Filter, but I think I would have to reset the filter set up each time new records were added. I am looking for something that would automatically add them to the second work sheet which is only picking up records with "Yellow" as the criteria.

[Norie]

Matt

You can't filter and automatically copy to a new sheet using Advanced Filter.

Of course you could filter in-place and then manually do the copy.

[sandy666]

Your profile says Ex2003
Your file says Ex2007 or higher

so I don't know it will work for you but you can try

done with PowerQuery (Get&Transform)
after update your source table and to see changes in other tables right click on these tables and select Refresh

[Gooford]

Hi

Sorry I hadn't logged on for a couple of years, I now have Excel 2013.

Thanks for responding to this. When I add a record and refresh I get the message "initialisation of data source failed', so I can't get it to work.

[sandy666]

1. change excel version in your profile
2. for Ex2013 Pro Plus you;ll need to download and install PowerQuery add-in from MS site. It's free. (or upgrade to Ex2016 or higher with PQ built-in)

after that all should work